CREATE TABLE `admin_events` (
	`id` int AUTO_INCREMENT NOT NULL,
	`eventType` enum('new_user','traffic_spike') NOT NULL,
	`details` text NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `admin_events_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `captcha_challenges` (
	`id` varchar(36) NOT NULL,
	`videoId` int NOT NULL,
	`ipHash` varchar(128) NOT NULL,
	`question` varchar(64) NOT NULL,
	`answerHash` varchar(128) NOT NULL,
	`expiresAt` timestamp NOT NULL,
	`usedAt` timestamp,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `captcha_challenges_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `videos` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`hash` varchar(24) NOT NULL,
	`sourceUrl` varchar(2048) NOT NULL,
	`platform` enum('youtube','facebook','tiktok','instagram','kwai') NOT NULL,
	`title` varchar(140),
	`isActive` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `videos_id` PRIMARY KEY(`id`),
	CONSTRAINT `videos_hash_unique` UNIQUE(`hash`)
);
--> statement-breakpoint
CREATE TABLE `visitor_accesses` (
	`id` int AUTO_INCREMENT NOT NULL,
	`videoId` int NOT NULL,
	`ipHash` varchar(128) NOT NULL,
	`accessDate` date NOT NULL,
	`accessedAt` timestamp NOT NULL DEFAULT (now()),
	`adblockDetected` boolean NOT NULL DEFAULT false,
	`captchaPassed` boolean NOT NULL DEFAULT false,
	`isEligible` boolean NOT NULL DEFAULT false,
	`blockReason` enum('none','adblock','access_limit','captcha_invalid') NOT NULL DEFAULT 'none',
	CONSTRAINT `visitor_accesses_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
ALTER TABLE `captcha_challenges` ADD CONSTRAINT `captcha_challenges_videoId_videos_id_fk` FOREIGN KEY (`videoId`) REFERENCES `videos`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `videos` ADD CONSTRAINT `videos_userId_users_id_fk` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `visitor_accesses` ADD CONSTRAINT `visitor_accesses_videoId_videos_id_fk` FOREIGN KEY (`videoId`) REFERENCES `videos`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `admin_events_created_idx` ON `admin_events` (`eventType`,`createdAt`);--> statement-breakpoint
CREATE INDEX `challenge_video_visitor_idx` ON `captcha_challenges` (`videoId`,`ipHash`);--> statement-breakpoint
CREATE INDEX `challenge_expiry_idx` ON `captcha_challenges` (`expiresAt`);--> statement-breakpoint
CREATE INDEX `videos_user_created_idx` ON `videos` (`userId`,`createdAt`);--> statement-breakpoint
CREATE INDEX `accesses_video_date_idx` ON `visitor_accesses` (`videoId`,`accessDate`);--> statement-breakpoint
CREATE INDEX `accesses_visitor_video_date_idx` ON `visitor_accesses` (`ipHash`,`videoId`,`accessDate`);--> statement-breakpoint
CREATE INDEX `accesses_accessed_at_idx` ON `visitor_accesses` (`accessedAt`);