CREATE TABLE `automation_runs` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`workflow_id` text NOT NULL,
	`status` text NOT NULL,
	`affected_type` text,
	`affected_id` text,
	`error_code` text,
	`started_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`completed_at` text,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`workflow_id`) REFERENCES `workflows`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_automation_runs_workspace_status` ON `automation_runs` (`workspace_id`,`status`,`started_at`);--> statement-breakpoint
CREATE TABLE `customer_contacts` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`full_name` text NOT NULL,
	`email` text DEFAULT '' NOT NULL,
	`phone` text DEFAULT '' NOT NULL,
	`company` text DEFAULT '' NOT NULL,
	`source` text DEFAULT 'manual' NOT NULL,
	`owner_user_id` text,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_customer_contacts_workspace_updated` ON `customer_contacts` (`workspace_id`,`updated_at`);--> statement-breakpoint
CREATE TABLE `customer_leads` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`contact_id` text,
	`title` text NOT NULL,
	`stage` text DEFAULT 'new' NOT NULL,
	`score` integer DEFAULT 0 NOT NULL,
	`source` text DEFAULT 'manual' NOT NULL,
	`interest` text DEFAULT '' NOT NULL,
	`owner_user_id` text,
	`value_minor` integer,
	`currency` text DEFAULT 'SAR' NOT NULL,
	`next_action_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`contact_id`) REFERENCES `customer_contacts`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `idx_customer_leads_workspace_stage` ON `customer_leads` (`workspace_id`,`stage`,`updated_at`);--> statement-breakpoint
CREATE INDEX `idx_customer_leads_workspace_owner` ON `customer_leads` (`workspace_id`,`owner_user_id`);--> statement-breakpoint
CREATE TABLE `workspace_notifications` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`user_id` text,
	`category` text NOT NULL,
	`priority` text DEFAULT 'medium' NOT NULL,
	`title_ar` text NOT NULL,
	`title_en` text NOT NULL,
	`body_ar` text DEFAULT '' NOT NULL,
	`body_en` text DEFAULT '' NOT NULL,
	`href` text,
	`read_at` text,
	`archived_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_workspace_notifications_scope` ON `workspace_notifications` (`workspace_id`,`user_id`,`read_at`,`created_at`);--> statement-breakpoint
CREATE TABLE `workspace_tasks` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`title` text NOT NULL,
	`kind` text DEFAULT 'task' NOT NULL,
	`status` text DEFAULT 'open' NOT NULL,
	`priority` text DEFAULT 'medium' NOT NULL,
	`assigned_user_id` text,
	`related_type` text,
	`related_id` text,
	`due_at` text,
	`created_by` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_workspace_tasks_workspace_status_due` ON `workspace_tasks` (`workspace_id`,`status`,`due_at`);--> statement-breakpoint
CREATE INDEX `idx_workspace_tasks_assignee` ON `workspace_tasks` (`workspace_id`,`assigned_user_id`);--> statement-breakpoint
CREATE TABLE `workspace_user_preferences` (
	`user_id` text PRIMARY KEY NOT NULL,
	`current_workspace_id` text,
	`language` text DEFAULT 'ar' NOT NULL,
	`timezone` text DEFAULT 'Asia/Riyadh' NOT NULL,
	`default_range` text DEFAULT '7d' NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`current_workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
DROP INDEX `idx_workspaces_owner_user_id`;--> statement-breakpoint
CREATE INDEX `idx_workspaces_owner_user_id` ON `workspaces` (`owner_user_id`);--> statement-breakpoint
PRAGMA optimize;