CREATE TABLE `conversation_assignments` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`conversation_id` text NOT NULL,
	`from_user_id` text,
	`to_user_id` text,
	`from_mode` text,
	`to_mode` text NOT NULL,
	`reason` text DEFAULT '' NOT NULL,
	`actor_user_id` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_conversation_assignments_thread` ON `conversation_assignments` (`workspace_id`,`conversation_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `conversation_internal_notes` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`conversation_id` text NOT NULL,
	`author_user_id` text NOT NULL,
	`author_name` text NOT NULL,
	`content` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_conversation_notes_thread` ON `conversation_internal_notes` (`workspace_id`,`conversation_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `conversation_sla_events` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`conversation_id` text NOT NULL,
	`target_type` text NOT NULL,
	`target_at` text NOT NULL,
	`breached_at` text,
	`cleared_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_conversation_sla_open` ON `conversation_sla_events` (`workspace_id`,`target_at`,`cleared_at`);--> statement-breakpoint
CREATE TABLE `inbox_saved_replies` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`title_ar` text NOT NULL,
	`title_en` text NOT NULL,
	`body_ar` text NOT NULL,
	`body_en` text NOT NULL,
	`category` text DEFAULT 'general' NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_by` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_saved_replies_workspace` ON `inbox_saved_replies` (`workspace_id`,`status`,`category`);--> statement-breakpoint
CREATE TABLE `inbox_webhook_events` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`channel_id` text NOT NULL,
	`external_event_id` text NOT NULL,
	`event_type` text NOT NULL,
	`payload_hash` text NOT NULL,
	`status` text DEFAULT 'received' NOT NULL,
	`processed_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_inbox_webhook_dedupe` ON `inbox_webhook_events` (`workspace_id`,`channel_id`,`external_event_id`);--> statement-breakpoint
CREATE INDEX `idx_inbox_webhook_status` ON `inbox_webhook_events` (`status`,`created_at`);--> statement-breakpoint
CREATE TABLE `message_attachments` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`conversation_id` text NOT NULL,
	`message_id` text,
	`object_key` text NOT NULL,
	`original_name` text NOT NULL,
	`mime_type` text NOT NULL,
	`size_bytes` integer NOT NULL,
	`scan_status` text DEFAULT 'pending' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_message_attachments_thread` ON `message_attachments` (`workspace_id`,`conversation_id`);--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `message_type` text DEFAULT 'text' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `external_message_id` text;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `channel_id` text;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `status` text DEFAULT 'sent' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `original_content` text;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `translated_content` text;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `metadata_json` text DEFAULT '{}' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversation_messages` ADD `idempotency_key` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `contact_id` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `assigned_user_id` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `assigned_team_id` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `owner_mode` text DEFAULT 'ai' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `queue_key` text DEFAULT 'general' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `tags_json` text DEFAULT '[]' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `language` text DEFAULT 'ar' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `handoff_reason` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `summary` text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `snoozed_until` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `resolved_at` text;--> statement-breakpoint
ALTER TABLE `conversations` ADD `last_message_at` text;--> statement-breakpoint
UPDATE `conversations` SET `last_message_at`=`updated_at` WHERE `last_message_at` IS NULL;--> statement-breakpoint
ALTER TABLE `conversations` ADD `lock_version` integer DEFAULT 0 NOT NULL;--> statement-breakpoint
PRAGMA optimize;