CREATE TABLE `crm_activities` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`entity_type` text NOT NULL,
	`entity_id` text NOT NULL,
	`kind` text NOT NULL,
	`title` text NOT NULL,
	`details_json` text DEFAULT '{}' NOT NULL,
	`actor_type` text DEFAULT 'user' NOT NULL,
	`actor_id` text,
	`agent_version_id` text,
	`conversation_id` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_crm_activities_entity` ON `crm_activities` (`workspace_id`,`entity_type`,`entity_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `idx_crm_activities_workspace` ON `crm_activities` (`workspace_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `crm_contact_merges` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`primary_contact_id` text NOT NULL,
	`secondary_contact_id` text NOT NULL,
	`snapshot_json` text NOT NULL,
	`merged_by` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_crm_contact_merges_workspace` ON `crm_contact_merges` (`workspace_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `crm_custom_fields` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`scope` text NOT NULL,
	`key` text NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`field_type` text NOT NULL,
	`options_json` text DEFAULT '[]' NOT NULL,
	`required` integer DEFAULT false NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_crm_custom_fields_scope_key` ON `crm_custom_fields` (`workspace_id`,`scope`,`key`);--> statement-breakpoint
CREATE TABLE `crm_notes` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`entity_type` text NOT NULL,
	`entity_id` text NOT NULL,
	`content` text NOT NULL,
	`author_user_id` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_crm_notes_entity` ON `crm_notes` (`workspace_id`,`entity_type`,`entity_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `crm_saved_views` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`user_id` text NOT NULL,
	`name` text NOT NULL,
	`entity_type` text NOT NULL,
	`filters_json` text DEFAULT '{}' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_crm_saved_views_user` ON `crm_saved_views` (`workspace_id`,`user_id`,`entity_type`);--> statement-breakpoint
CREATE TABLE `crm_tags` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`name` text NOT NULL,
	`color` text DEFAULT '#6366f1' NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_crm_tags_workspace_name` ON `crm_tags` (`workspace_id`,`name`);--> statement-breakpoint
CREATE TABLE `customer_companies` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`name` text NOT NULL,
	`industry` text DEFAULT '' NOT NULL,
	`website` text DEFAULT '' NOT NULL,
	`country` text DEFAULT '' NOT NULL,
	`city` text DEFAULT '' NOT NULL,
	`size` text DEFAULT '' NOT NULL,
	`main_contact_id` text,
	`owner_user_id` text,
	`tags_json` text DEFAULT '[]' NOT NULL,
	`notes` text DEFAULT '' NOT NULL,
	`custom_fields_json` text DEFAULT '{}' NOT NULL,
	`archived_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
);
--> statement-breakpoint
CREATE INDEX `idx_customer_companies_workspace_name` ON `customer_companies` (`workspace_id`,`name`);--> statement-breakpoint
CREATE INDEX `idx_customer_companies_workspace_owner` ON `customer_companies` (`workspace_id`,`owner_user_id`);--> statement-breakpoint
CREATE TABLE `sales_deals` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`name` text NOT NULL,
	`contact_id` text,
	`company_id` text,
	`source_lead_id` text,
	`conversation_id` text,
	`pipeline_id` text NOT NULL,
	`stage_id` text NOT NULL,
	`value_minor` integer,
	`currency` text DEFAULT 'SAR' NOT NULL,
	`owner_user_id` text,
	`probability` integer,
	`expected_close_at` text,
	`source` text DEFAULT 'manual' NOT NULL,
	`next_action` text DEFAULT '' NOT NULL,
	`lost_reason` text,
	`won_at` text,
	`lost_at` text,
	`last_activity_at` text,
	`archived_at` text,
	`lock_version` integer DEFAULT 0 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_sales_deals_pipeline_stage` ON `sales_deals` (`workspace_id`,`pipeline_id`,`stage_id`);--> statement-breakpoint
CREATE INDEX `idx_sales_deals_owner` ON `sales_deals` (`workspace_id`,`owner_user_id`);--> statement-breakpoint
CREATE INDEX `idx_sales_deals_contact` ON `sales_deals` (`workspace_id`,`contact_id`);--> statement-breakpoint
CREATE TABLE `sales_pipeline_stages` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`pipeline_id` text NOT NULL,
	`key` text NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`color` text DEFAULT '#6366f1' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`probability` integer,
	`is_won` integer DEFAULT false NOT NULL,
	`is_lost` integer DEFAULT false NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`pipeline_id`) REFERENCES `sales_pipelines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_pipeline_stage_key` ON `sales_pipeline_stages` (`pipeline_id`,`key`);--> statement-breakpoint
CREATE INDEX `idx_pipeline_stages_order` ON `sales_pipeline_stages` (`workspace_id`,`pipeline_id`,`sort_order`);--> statement-breakpoint
CREATE TABLE `sales_pipelines` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`is_default` integer DEFAULT false NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`sort_order` integer DEFAULT 0 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_sales_pipelines_workspace` ON `sales_pipelines` (`workspace_id`,`status`,`sort_order`);--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `company_id` text;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `country` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `language` text DEFAULT 'ar' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `external_channel_id` text;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `tags_json` text DEFAULT '[]' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `custom_fields_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `last_activity_at` text;--> statement-breakpoint
ALTER TABLE `customer_contacts` ADD `archived_at` text;--> statement-breakpoint
CREATE INDEX `idx_customer_contacts_workspace_email` ON `customer_contacts` (`workspace_id`,`email`);--> statement-breakpoint
CREATE INDEX `idx_customer_contacts_workspace_phone` ON `customer_contacts` (`workspace_id`,`phone`);--> statement-breakpoint
CREATE INDEX `idx_customer_contacts_workspace_company` ON `customer_contacts` (`workspace_id`,`company_id`);--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `company_id` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `conversation_id` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `agent_version_id` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `priority` text DEFAULT 'normal' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `qualification_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `last_activity_at` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `converted_at` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `converted_by` text;--> statement-breakpoint
ALTER TABLE `customer_leads` ADD `archived_at` text;
--> statement-breakpoint
PRAGMA optimize;