
CREATE TABLE `bids` (
  `pkey` int(11) NOT NULL,
  `advertiserId` varchar(255) NOT NULL,
  `expectedHashes` text NOT NULL,
  `maxPrice` double NOT NULL DEFAULT '0',
  `maxPriceCpm` double NOT NULL DEFAULT '0',
  `budgetId` int(11) NOT NULL,
  `size` varchar(255) NOT NULL,
  `format` VARCHAR(32) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `startDate` timestamp NULL DEFAULT NULL,
  `endDate` timestamp NULL DEFAULT NULL,
  `browsersInclude` varchar(255) NOT NULL DEFAULT '',
  `browsersExclude` varchar(255) NOT NULL DEFAULT '',
  `operatingSystemsInclude` varchar(255) NOT NULL DEFAULT '',
  `operatingSystemsExclude` varchar(255) NOT NULL DEFAULT '',
  `deviceMakeAndModelInclude` text,
  `deviceMakeAndModelExclude` text,
  `environments` varchar(255) NOT NULL DEFAULT '',
  `connectionType` varchar(255) NOT NULL DEFAULT '',
  `countriesInclude` varchar(255) NOT NULL DEFAULT '',
  `countriesExclude` varchar(255) NOT NULL DEFAULT '',
  `languagesInclude` varchar(255) NOT NULL DEFAULT '',
  `languagesExclude` varchar(255) NOT NULL DEFAULT '',
  `budgetDistribution` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = accelerated, 1 = even',
  `sliderMonday` int(11) NOT NULL DEFAULT '100',
  `sliderTuesday` int(11) NOT NULL DEFAULT '100',
  `sliderWednesday` int(11) NOT NULL DEFAULT '100',
  `sliderThursday` int(11) NOT NULL DEFAULT '100',
  `sliderFriday` int(11) NOT NULL DEFAULT '100',
  `sliderSaturday` int(11) NOT NULL DEFAULT '100',
  `sliderSunday` int(11) NOT NULL DEFAULT '100',
  `sliderTimes` varchar(255) NOT NULL DEFAULT '0,24',
  `cities` varchar(2048) NOT NULL,
  `regions` varchar(2048) NOT NULL,
  `domainsInclude` text NOT NULL,
  `domainsExclude` text NOT NULL,
  `publishersExclude` text NOT NULL,
  `stocksData` text NOT NULL,
  `stocksDataUpdated` date DEFAULT NULL,
  `sportsData` text,
  `sportsTargetingCountriesEnabled` tinyint(1) NOT NULL DEFAULT '0',
  `eventData` text,
  `holidaysData` text,
  `abstract` text,
  `keywords` text,
  `keywordsExclude` text,
  `sentiment` VARCHAR(64) NOT NULL DEFAULT '',
  `contextualAccuracy` int(11) NULL DEFAULT NULL,
  `contextualBidMpl` int(11) NULL DEFAULT NULL,
  `h0` int(11) NOT NULL DEFAULT '100',
  `h1` int(11) NOT NULL DEFAULT '100',
  `h2` int(11) NOT NULL DEFAULT '100',
  `h3` int(11) NOT NULL DEFAULT '100',
  `h4` int(11) NOT NULL DEFAULT '100',
  `h5` int(11) NOT NULL DEFAULT '100',
  `h6` int(11) NOT NULL DEFAULT '100',
  `h7` int(11) NOT NULL DEFAULT '100',
  `h8` int(11) NOT NULL DEFAULT '100',
  `h9` int(11) NOT NULL DEFAULT '100',
  `h10` int(11) NOT NULL DEFAULT '100',
  `h11` int(11) NOT NULL DEFAULT '100',
  `h12` int(11) NOT NULL DEFAULT '100',
  `h13` int(11) NOT NULL DEFAULT '100',
  `h14` int(11) NOT NULL DEFAULT '100',
  `h15` int(11) NOT NULL DEFAULT '100',
  `h16` int(11) NOT NULL DEFAULT '100',
  `h17` int(11) NOT NULL DEFAULT '100',
  `h18` int(11) NOT NULL DEFAULT '100',
  `h19` int(11) NOT NULL DEFAULT '100',
  `h20` int(11) NOT NULL DEFAULT '100',
  `h21` int(11) NOT NULL DEFAULT '100',
  `h22` int(11) NOT NULL DEFAULT '100',
  `h23` int(11) NOT NULL DEFAULT '100',
  `continents` varchar(255) NOT NULL DEFAULT '',
  `aiId` int NOT NULL,
  `isAiBid` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `bids`
  ADD PRIMARY KEY (`pkey`);

ALTER TABLE `bids`
  MODIFY `pkey` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `budgets` (
  `advertiser` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `id` int NOT NULL,
  `budget` double NOT NULL,
  `budgetUsed` double NOT NULL DEFAULT '0',
  `frequencyCapping` int NOT NULL,
  `recencyCapping` int NOT NULL,
  `directDeal` int NOT NULL DEFAULT '0',
  `usesPositiveHypercontextual` tinyint(1) NOT NULL DEFAULT '0',
  `usesPositiveStocksData` tinyint(1) NOT NULL DEFAULT '0',
  `usesPositiveSportsData` tinyint(1) NOT NULL DEFAULT '0',
  `usesPositiveEventData` tinyint(1) NOT NULL DEFAULT '0',
  `usesPositiveHolidaysData` tinyint(1) NOT NULL DEFAULT '0',
  `usesPositiveContextualUrlData` tinyint(1) DEFAULT '0',
  `useOnlyContextualUrlDataForRTB` tinyint(1) NOT NULL DEFAULT '0',
  `additionalSportTargetingCountries` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `emailNotification` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `emailCompleted` tinyint(1) NOT NULL DEFAULT '0',
  `postCodesInclude` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `postCodesExclude` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `aiId` int NOT NULL
);

ALTER TABLE `budgets`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `budgets`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;


CREATE TABLE `cities` (
  `id` int NOT NULL,
  `iso2` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `cityAlt` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `region` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `population` int DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `lon` double DEFAULT NULL,
  `weatherCondCurrent` varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherTempCurrentMin` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `weatherTempCurrentMax` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `weatherUviCurrent` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `weatherWindSpeedCurrent` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `weatherIconCurrent` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherCondForecast1d` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherTempForecast1dMin` decimal(5,2) DEFAULT NULL,
  `weatherTempForecast1dMax` decimal(5,2) DEFAULT NULL,
  `weatherUviForecast1d` int DEFAULT NULL,
  `weatherWindSpeedForecast1d` decimal(3,1) DEFAULT NULL,
  `weatherIconForecast1d` varchar(6) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherCondForecast2d` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherTempForecast2dMin` decimal(5,2) DEFAULT NULL,
  `weatherTempForecast2dMax` decimal(5,2) DEFAULT NULL,
  `weatherUviForecast2d` int DEFAULT NULL,
  `weatherWindSpeedForecast2d` decimal(3,1) DEFAULT NULL,
  `weatherIconForecast2d` varchar(6) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherCondForecast3d` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `weatherTempForecast3dMin` decimal(5,2) DEFAULT NULL,
  `weatherTempForecast3dMax` decimal(5,2) DEFAULT NULL,
  `weatherUviForecast3d` int DEFAULT NULL,
  `weatherWindSpeedForecast3d` decimal(3,1) DEFAULT NULL,
  `weatherIconForecast3d` varchar(6) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `extremeWeatherStart` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `extremeWeatherEnd` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;

ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `cities`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

CREATE TABLE `bids_ai` (
  `id` int NOT NULL,
  `browsers` tinyint(1) NOT NULL,
  `os` tinyint(1) NOT NULL,
  `devices` tinyint(1) NOT NULL,
  `time` tinyint(1) NOT NULL,
  `weather` tinyint(1) NOT NULL,
  `done` tinyint(1) NOT NULL DEFAULT '0'
);

ALTER TABLE `bids_ai`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `bids_ai`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

INSERT INTO `cities` (`iso2`, `city`, `cityAlt`, `region`, `population`, `lat`, `lon`, `weatherCondCurrent`, `weatherTempCurrentMin`, `weatherTempCurrentMax`, `weatherUviCurrent`, `weatherWindSpeedCurrent`, `weatherIconCurrent`, `weatherCondForecast1d`, `weatherTempForecast1dMin`, `weatherTempForecast1dMax`, `weatherUviForecast1d`, `weatherWindSpeedForecast1d`, `weatherIconForecast1d`, `weatherCondForecast2d`, `weatherTempForecast2dMin`, `weatherTempForecast2dMax`, `weatherUviForecast2d`, `weatherWindSpeedForecast2d`, `weatherIconForecast2d`, `weatherCondForecast3d`, `weatherTempForecast3dMin`, `weatherTempForecast3dMax`, `weatherUviForecast3d`, `weatherWindSpeedForecast3d`, `weatherIconForecast3d`, `extremeWeatherStart`, `extremeWeatherEnd`, `lastUpdated`) VALUES
('GB', 'London', NULL, 'Greater London', 9787426, 51.51069, -0.11496, '[\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Clouds\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\",\"Rain\"]', '[7.26,7.36,7.47,7.66,7.77,7.75,7.42,7.31,7.01,7.47,8.68,7.98,7.84,7.26,5.49,4.21,4.38,5.23,5.76,5.58,5.47,5.38,5.16,5.17]', '[7.26,7.36,7.47,7.66,7.77,7.75,7.42,7.31,7.01,7.47,8.68,7.98,7.84,7.26,5.49,4.21,4.38,5.23,5.76,5.58,5.47,5.38,5.16,5.17]', '[0.27,0.38,0.37,0.33,0.21,0.06,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.1]', '[3.09,2.96,2.34,2.23,1.6,2.47,3.36,4.1,6.66,5.28,3.4,1.69,1.65,3.85,7.46,8.41,8.18,7.4,5.88,5.26,4.65,4.22,3.89,3.61]', '[\"04d\",\"04d\",\"04d\",\"04d\",\"04d\",\"04d\",\"10d\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"10n\",\"04n\",\"04n\",\"04n\",\"04n\",\"04d\"]', 'Rain', '4.21', '6.89', 0, '8.4', '10d', 'Clouds', '4.37', '6.25', 0, '3.6', '04d', 'Clouds', '2.51', '5.65', 0, '4.8', '04d', '1704369600', '1704423600', '2024-01-04 10:32:02');
