CREATE TABLE IF NOT EXISTS `live_chat_conversations` (
  `id` text PRIMARY KEY NOT NULL,
  `customer_id` text NOT NULL,
  `assigned_to` text,
  `status` text DEFAULT 'OPEN' NOT NULL,
  `last_message` text,
  `last_message_at` integer NOT NULL,
  `staff_unread` integer DEFAULT 0 NOT NULL,
  `customer_unread` integer DEFAULT 0 NOT NULL,
  `created_at` integer NOT NULL,
  `updated_at` integer NOT NULL,
  `closed_at` integer,
  FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`)
);
--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS `idx_live_chat_customer_open` ON `live_chat_conversations` (`customer_id`) WHERE `status` = 'OPEN';
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS `idx_live_chat_status_last_message` ON `live_chat_conversations` (`status`,`last_message_at`);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS `live_chat_messages` (
  `id` text PRIMARY KEY NOT NULL,
  `conversation_id` text NOT NULL,
  `sender_id` text NOT NULL,
  `sender_role` text NOT NULL,
  `body` text NOT NULL,
  `created_at` integer NOT NULL,
  `read_at` integer,
  `deleted_at` integer,
  FOREIGN KEY (`conversation_id`) REFERENCES `live_chat_conversations`(`id`),
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`)
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS `idx_live_chat_messages_conversation_created` ON `live_chat_messages` (`conversation_id`,`created_at`) WHERE `deleted_at` IS NULL;
--> statement-breakpoint
PRAGMA optimize;