-- Thoughtful Journey standalone initial schema
-- Import this file once into a new MySQL database.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int AUTO_INCREMENT NOT NULL,
  `openId` varchar(64) NOT NULL,
  `name` text,
  `email` varchar(320),
  `loginMethod` varchar(64),
  `role` enum('user','admin') NOT NULL DEFAULT 'user',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastSignedIn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_openId_unique` (`openId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `quotes` (
  `id` int AUTO_INCREMENT NOT NULL,
  `body` text NOT NULL,
  `author` varchar(160),
  `category` varchar(80) NOT NULL,
  `fingerprint` varchar(64) NOT NULL,
  `randomKey` int NOT NULL,
  `isActive` boolean NOT NULL DEFAULT true,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotes_fingerprint_unique` (`fingerprint`),
  KEY `quotes_active_random_idx` (`isActive`,`randomKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `quoteImportJobs` (
  `id` varchar(36) NOT NULL,
  `sourceFileName` varchar(255) NOT NULL,
  `status` enum('running','completed','failed') NOT NULL DEFAULT 'running',
  `totalRows` int NOT NULL DEFAULT 0,
  `processedRows` int NOT NULL DEFAULT 0,
  `insertedRows` int NOT NULL DEFAULT 0,
  `duplicateRows` int NOT NULL DEFAULT 0,
  `invalidRows` int NOT NULL DEFAULT 0,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completedAt` timestamp NULL,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `quoteImportJobs_createdAt_idx` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `adCampaigns` (
  `id` int AUTO_INCREMENT NOT NULL,
  `title` varchar(180) NOT NULL,
  `altText` varchar(250),
  `imageUrl` text NOT NULL,
  `imageKey` varchar(512),
  `targetUrl` varchar(2048) NOT NULL,
  `utmSource` varchar(120) NOT NULL DEFAULT 'quiet_signal',
  `utmMedium` varchar(120) NOT NULL DEFAULT 'affiliate',
  `utmCampaign` varchar(160),
  `utmContent` varchar(160),
  `placement` enum('top','right','bottom','left') NOT NULL,
  `sortOrder` int NOT NULL DEFAULT 0,
  `rotationSeconds` int NOT NULL DEFAULT 0,
  `isActive` boolean NOT NULL DEFAULT true,
  `startsAt` timestamp NULL,
  `endsAt` timestamp NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `adFrameSettings` (
  `id` int NOT NULL,
  `desktopBannerWidth` int NOT NULL DEFAULT 800,
  `desktopBannerHeight` int NOT NULL DEFAULT 82,
  `desktopRailWidth` int NOT NULL DEFAULT 144,
  `desktopRailHeight` int NOT NULL DEFAULT 380,
  `desktopGutter` int NOT NULL DEFAULT 28,
  `mobileBannerWidth` int NOT NULL DEFAULT 350,
  `mobileBannerHeight` int NOT NULL DEFAULT 56,
  `mobileRailWidth` int NOT NULL DEFAULT 54,
  `mobileRailHeight` int NOT NULL DEFAULT 230,
  `mobileGutter` int NOT NULL DEFAULT 10,
  `mobileShowBanners` boolean NOT NULL DEFAULT false,
  `mobileShowRails` boolean NOT NULL DEFAULT false,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `adEvents` (
  `id` int AUTO_INCREMENT NOT NULL,
  `campaignId` int NOT NULL,
  `eventType` enum('impression','click') NOT NULL,
  `placement` enum('top','right','bottom','left') NOT NULL,
  `utmSource` varchar(120) NOT NULL,
  `utmMedium` varchar(120) NOT NULL,
  `utmCampaign` varchar(160) NOT NULL,
  `utmContent` varchar(160) NOT NULL,
  `occurredAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `adEvents_occurredAt_idx` (`occurredAt`),
  KEY `adEvents_campaignId_idx` (`campaignId`),
  KEY `adEvents_utm_idx` (`utmSource`,`utmMedium`,`utmCampaign`,`utmContent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `adFrameSettings` (`id`) VALUES (1);

INSERT IGNORE INTO `quotes` (`body`,`author`,`category`,`fingerprint`,`randomKey`,`isActive`) VALUES
('Begin again, but begin gently.', NULL, 'Gentleness', SHA2(CONCAT(LOWER(TRIM('Begin again, but begin gently.')), CHAR(0), ''),256), 102938475, true),
('The next right thing can be small enough to fit in one breath.', NULL, 'Presence', SHA2(CONCAT(LOWER(TRIM('The next right thing can be small enough to fit in one breath.')), CHAR(0), ''),256), 203948576, true),
('Rest is not a detour from the journey; it is part of the road.', NULL, 'Rest', SHA2(CONCAT(LOWER(TRIM('Rest is not a detour from the journey; it is part of the road.')), CHAR(0), ''),256), 304958677, true),
('You do not have to carry the whole horizon today.', NULL, 'Perspective', SHA2(CONCAT(LOWER(TRIM('You do not have to carry the whole horizon today.')), CHAR(0), ''),256), 405968778, true),
('A quiet decision still changes the shape of a life.', NULL, 'Courage', SHA2(CONCAT(LOWER(TRIM('A quiet decision still changes the shape of a life.')), CHAR(0), ''),256), 506978879, true),
('Make room for the version of you that is still becoming.', NULL, 'Growth', SHA2(CONCAT(LOWER(TRIM('Make room for the version of you that is still becoming.')), CHAR(0), ''),256), 607988980, true),
('Some answers arrive only after the question has been allowed to rest.', NULL, 'Patience', SHA2(CONCAT(LOWER(TRIM('Some answers arrive only after the question has been allowed to rest.')), CHAR(0), ''),256), 708999081, true),
('There is no urgency in becoming more yourself.', NULL, 'Self-trust', SHA2(CONCAT(LOWER(TRIM('There is no urgency in becoming more yourself.')), CHAR(0), ''),256), 809109182, true),
('Notice what becomes possible when you stop arguing with the present moment.', NULL, 'Acceptance', SHA2(CONCAT(LOWER(TRIM('Notice what becomes possible when you stop arguing with the present moment.')), CHAR(0), ''),256), 910219283, true),
('Let the day be unfinished. You are allowed to be unfinished too.', NULL, 'Compassion', SHA2(CONCAT(LOWER(TRIM('Let the day be unfinished. You are allowed to be unfinished too.')), CHAR(0), ''),256), 1011329384, true),
('Small kindnesses are often the most accurate map home.', NULL, 'Kindness', SHA2(CONCAT(LOWER(TRIM('Small kindnesses are often the most accurate map home.')), CHAR(0), ''),256), 1112439485, true),
('Your pace is still a pace.', NULL, 'Steadiness', SHA2(CONCAT(LOWER(TRIM('Your pace is still a pace.')), CHAR(0), ''),256), 1213549586, true);