
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 `endpoints` (
  `hash` char(42) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `endpoint` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `notes` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `role` int NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `legalNames` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `legalProof` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `website` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `depositAmount` double NOT NULL DEFAULT '0',
  `additionalCostWeather` double NOT NULL DEFAULT '0',
  `additionalCostSports` double NOT NULL DEFAULT '0',
  `additionalCostStocks` double NOT NULL DEFAULT '0',
  `additionalCostHolidays` double NOT NULL DEFAULT '0',
  `additionalCostHypercontextual` double NOT NULL DEFAULT '0',
  `currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'EUR',
  `votingParticipant` tinyint(1) NOT NULL DEFAULT '0',
  `votingRank` int NOT NULL DEFAULT '25',
  `vatNumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `invoiceCountry` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `invoiceAddress` varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `paymentTerms` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `contactName` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `timezone` int NOT NULL DEFAULT '0',
  `private` char(66) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `solana_private_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `solana_public_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `solana_member_private_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `solana_member_public_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `categories` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `keywords` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `apiVersion` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cloudDatabase` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `balanceAD` double NOT NULL DEFAULT '0',
  `balanceETH` double NOT NULL DEFAULT '0',
  `linked` char(42) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Linked platform hash',
  `notification_mediums` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '{"platform":1,"email":0}',
  `disabled_notifications` varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '{"campaign_ending_soon":"1","direct_deals":"1","new_dd":"1","accepted_dd":"1","declined_dd":"1","paused_dd":"1","resumed_dd":"1"}',
  `brand_safety_max_score` tinyint(1) NOT NULL DEFAULT '6',
  `googleId` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `adManager` tinyint(1) NOT NULL DEFAULT '0',
  `adManagerNetworkName` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `mode` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'standard',
  `desiredMode` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'standard',
  `invoicesCount` int NOT NULL DEFAULT '0',
  `creditNotesCount` int NOT NULL DEFAULT '0',
  `businessUnits_id` int NOT NULL DEFAULT '0',
  `hostingFeeDate` date DEFAULT NULL,
  `hasBubbleUnit` tinyint(1) NOT NULL DEFAULT '0',
  `seenFormats` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '{"formats":""}',
  `referredBy` char(42) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `billingEmail` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `selfHosted` tinyint(1) NOT NULL DEFAULT '0'
);


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 `endpoints` (`hash`, `endpoint`, `name`, `notes`, `role`, `dateAdded`, `active`, `legalNames`, `legalProof`, `website`, `depositAmount`, `additionalCostWeather`, `additionalCostSports`, `additionalCostStocks`, `additionalCostHolidays`, `additionalCostHypercontextual`, `currency`, `votingParticipant`, `votingRank`, `vatNumber`, `invoiceCountry`, `invoiceAddress`, `paymentTerms`, `email`, `phone`, `contactName`, `timezone`, `private`, `solana_private_key`, `solana_public_key`, `solana_member_private_key`, `solana_member_public_key`, `categories`, `keywords`, `apiVersion`, `cloudDatabase`, `balanceAD`, `balanceETH`, `linked`, `notification_mediums`, `disabled_notifications`, `brand_safety_max_score`, `googleId`, `adManager`, `adManagerNetworkName`, `mode`, `desiredMode`, `invoicesCount`, `creditNotesCount`, `businessUnits_id`, `hostingFeeDate`, `hasBubbleUnit`, `seenFormats`, `referredBy`, `billingEmail`, `selfHosted`) VALUES
  ('0x49db5b7efa9f6d5668f241b96c3d8f7cfaac7c65', 'https://adhash.org/a/adhash/protocol.php', 'AdHash', '', 2, '2019-11-20 00:00:00', 1, 'AdHash EOOD', '', 'https://adhash.org/', 557.5993116572628, 0, 0, 0, 0, 0, 'BGN', 0, 25, 'BG205404671', 'Bulgaria', 'str. Golo bardo 22, Sofia 1407', 'immediately', 'office@adhash.com', '', 'AdHash Team', 2, '', 'pHVHNMQht7nQJXAoUWoAnhNLHcYQw2QqQbbn9oycV9sxJ6Y2xa8fcV4koTwYsGZRJwvYWyRw7RxqqFh1Ru7jRbg', '8nNYunD67t9ofYd2pTjQNRDBw2t3uKGBPb8CeH7HnGdY', '3grCJAxzSNVSWPvXqceV4h3i7perr8oDyV6Ae3Prf1HDZU5cdZgvA7ju3zJSv6sn2MCPWcHjW7N5JXGCpjVoj4vb', '4XhmCBb6XAJyJNUY6FRoqYAAQkk3nLAw3sKVBLGsduZH', 'Pets and animal care', '', '3.9', 'adhash', 0, 0, NULL, '{\"platform\":\"1\",\"email\":\"1\"}', '{\"all_notifications\":1,\"campaign_related\":\"1\",\"no_spend_alert\":\"1\",\"underspend_alert\":\"1\",\"campaign_ending_soon\":\"1\",\"direct_deals\":\"1\",\"new_dd\":\"1\",\"accepted_dd\":\"1\",\"declined_dd\":\"1\",\"paused_dd\":\"1\",\"resumed_dd\":\"1\",\"delayed_reports\":\"1\",\"cancelled_dd\":1}', 30, '', 0, NULL, 'advanced', 'advanced', 218, 47, 4, '2023-01-02', 0, '[]', '', 'test@test.com', 0);

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');
