CREATE TABLE `geo_delivery_items` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`snapshot_id` text,
	`item_id` text NOT NULL,
	`title` text NOT NULL,
	`media` text NOT NULL,
	`media_level` text DEFAULT '' NOT NULL,
	`original_url` text NOT NULL,
	`publish_date` text,
	`index_status` text DEFAULT '待检测' NOT NULL,
	`citation_status` text DEFAULT '待检测' NOT NULL,
	`ai_platforms` text DEFAULT '[]' NOT NULL,
	`sha256` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`snapshot_id`) REFERENCES `geo_snapshots`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE UNIQUE INDEX `geo_delivery_project_item_unique` ON `geo_delivery_items` (`project_id`,`item_id`);--> statement-breakpoint
CREATE INDEX `geo_delivery_snapshot_idx` ON `geo_delivery_items` (`snapshot_id`);--> statement-breakpoint
CREATE TABLE `geo_evidence_records` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`snapshot_id` text,
	`record_id` text NOT NULL,
	`prompt_id` text DEFAULT '' NOT NULL,
	`platform` text DEFAULT '' NOT NULL,
	`model_version` text DEFAULT '' NOT NULL,
	`tested_at` text DEFAULT '' NOT NULL,
	`question` text DEFAULT '' NOT NULL,
	`answer` text DEFAULT '' NOT NULL,
	`conversation_url` text,
	`evidence_object_key` text,
	`mention` integer DEFAULT 0 NOT NULL,
	`recommend` integer DEFAULT 0 NOT NULL,
	`top3` integer DEFAULT 0 NOT NULL,
	`rank` real,
	`sentiment` text,
	`citation_urls` text DEFAULT '[]' NOT NULL,
	`sha256` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`snapshot_id`) REFERENCES `geo_snapshots`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE UNIQUE INDEX `geo_evidence_project_record_unique` ON `geo_evidence_records` (`project_id`,`record_id`);--> statement-breakpoint
CREATE INDEX `geo_evidence_snapshot_idx` ON `geo_evidence_records` (`snapshot_id`);--> statement-breakpoint
CREATE TABLE `geo_import_jobs` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`snapshot_id` text,
	`import_type` text NOT NULL,
	`filename` text NOT NULL,
	`object_key` text,
	`status` text NOT NULL,
	`total_rows` integer DEFAULT 0 NOT NULL,
	`valid_rows` integer DEFAULT 0 NOT NULL,
	`error_rows` integer DEFAULT 0 NOT NULL,
	`errors_json` text DEFAULT '[]' NOT NULL,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`snapshot_id`) REFERENCES `geo_snapshots`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `geo_import_jobs_project_idx` ON `geo_import_jobs` (`project_id`);--> statement-breakpoint
CREATE TABLE `geo_projects` (
	`id` text PRIMARY KEY NOT NULL,
	`slug` text NOT NULL,
	`project_name` text NOT NULL,
	`brand_name` text NOT NULL,
	`client_name` text NOT NULL,
	`core_keyword` text DEFAULT '' NOT NULL,
	`start_date` text,
	`end_date` text,
	`baseline_month` text,
	`current_month` text,
	`status` text DEFAULT 'active' NOT NULL,
	`data_mode` text DEFAULT 'empty' NOT NULL,
	`logo_key` text,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `geo_projects_slug_unique` ON `geo_projects` (`slug`);--> statement-breakpoint
CREATE INDEX `geo_projects_status_idx` ON `geo_projects` (`status`);--> statement-breakpoint
CREATE TABLE `geo_snapshots` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`report_month` text NOT NULL,
	`version` integer DEFAULT 1 NOT NULL,
	`status` text DEFAULT 'draft' NOT NULL,
	`quality_score` real DEFAULT 0 NOT NULL,
	`answer_count` integer DEFAULT 0 NOT NULL,
	`delivery_count` integer DEFAULT 0 NOT NULL,
	`evidence_count` integer DEFAULT 0 NOT NULL,
	`source_filename` text,
	`summary_json` text DEFAULT '{}' NOT NULL,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	`published_at` text,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `geo_snapshots_project_idx` ON `geo_snapshots` (`project_id`);--> statement-breakpoint
CREATE INDEX `geo_snapshots_month_idx` ON `geo_snapshots` (`project_id`,`report_month`);--> statement-breakpoint
CREATE TABLE `workspace_users` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`display_name` text,
	`role` text DEFAULT 'viewer' NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `workspace_users_email_unique` ON `workspace_users` (`email`);