CREATE TABLE `geo_acceptance_records` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`snapshot_id` text NOT NULL,
	`status` text NOT NULL,
	`comment` text DEFAULT '' NOT NULL,
	`submitted_by` text NOT NULL,
	`submitted_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 cascade
);
--> statement-breakpoint
CREATE INDEX `geo_acceptance_snapshot_idx` ON `geo_acceptance_records` (`snapshot_id`);--> statement-breakpoint
CREATE TABLE `geo_audit_logs` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text,
	`actor_email` text NOT NULL,
	`action` text NOT NULL,
	`target_type` text NOT NULL,
	`target_id` text,
	`detail_json` text DEFAULT '{}' NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `geo_audit_project_idx` ON `geo_audit_logs` (`project_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `geo_competitor_records` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`snapshot_id` text NOT NULL,
	`record_id` text NOT NULL,
	`prompt_id` text NOT NULL,
	`platform` text NOT NULL,
	`competitor_name` text NOT NULL,
	`mentioned` integer DEFAULT 0 NOT NULL,
	`recommended` integer DEFAULT 0 NOT NULL,
	`rank` real,
	`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 cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `geo_competitor_snapshot_record_unique` ON `geo_competitor_records` (`snapshot_id`,`record_id`,`competitor_name`);--> statement-breakpoint
CREATE INDEX `geo_competitor_snapshot_idx` ON `geo_competitor_records` (`snapshot_id`);--> statement-breakpoint
CREATE TABLE `geo_project_config_versions` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`version` integer NOT NULL,
	`config_json` text NOT NULL,
	`fingerprint` text NOT NULL,
	`status` text DEFAULT 'draft' NOT NULL,
	`change_reason` text,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	`frozen_at` text,
	FOREIGN KEY (`project_id`) REFERENCES `geo_projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `geo_project_config_version_unique` ON `geo_project_config_versions` (`project_id`,`version`);--> statement-breakpoint
CREATE INDEX `geo_project_config_project_idx` ON `geo_project_config_versions` (`project_id`);--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `keyword` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `question_type` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `scene` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `region` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `accuracy` integer;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `accuracy_note` text;--> statement-breakpoint
ALTER TABLE `geo_evidence_records` ADD `judged_by` text;--> statement-breakpoint
ALTER TABLE `geo_project_members` ADD `display_name` text;--> statement-breakpoint
ALTER TABLE `geo_project_members` ADD `status` text DEFAULT 'active' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_project_members` ADD `updated_at` text;--> statement-breakpoint
ALTER TABLE `geo_projects` ADD `measurement_protocol_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_projects` ADD `config_version` integer DEFAULT 1 NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_projects` ADD `config_fingerprint` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_projects` ADD `frozen_at` text;--> statement-breakpoint
ALTER TABLE `geo_projects` ADD `frozen_by` text;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `scope_fingerprint` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `scope_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `gate_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `reviewed_by` text;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `review_note` text;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `published_by` text;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `acceptance_status` text DEFAULT 'pending' NOT NULL;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `accepted_at` text;--> statement-breakpoint
ALTER TABLE `geo_snapshots` ADD `accepted_by` text;