-- ============================================================
-- Recall AI – Complete Database Schema
-- Import this into MySQL via cPanel phpMyAdmin
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ─────────────────────────────────────────────────────────────
-- Table: plans
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `plans` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `price_monthly` decimal(10,2) NOT NULL DEFAULT 0.00,
  `price_yearly` decimal(10,2) NOT NULL DEFAULT 0.00,
  `stripe_monthly_price_id` varchar(255) DEFAULT NULL,
  `stripe_yearly_price_id` varchar(255) DEFAULT NULL,
  `max_gmail_accounts` int(11) NOT NULL DEFAULT 1,
  `max_whatsapp_accounts` int(11) NOT NULL DEFAULT 1,
  `max_messages_per_month` int(11) NOT NULL DEFAULT 500,
  `max_ai_extractions_per_month` int(11) NOT NULL DEFAULT 100,
  `has_smart_search` tinyint(1) NOT NULL DEFAULT 1,
  `has_daily_summary` tinyint(1) NOT NULL DEFAULT 1,
  `has_priority_support` tinyint(1) NOT NULL DEFAULT 0,
  `has_api_access` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plans_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: users
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `verification_token` varchar(255) DEFAULT NULL,
  `plan_id` bigint(20) UNSIGNED NOT NULL DEFAULT 1,
  `subscription_status` enum('active','inactive','cancelled','past_due') NOT NULL DEFAULT 'active',
  `stripe_customer_id` varchar(255) DEFAULT NULL,
  `stripe_subscription_id` varchar(255) DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `subscription_ends_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','suspended','banned') NOT NULL DEFAULT 'active',
  `is_admin` tinyint(1) NOT NULL DEFAULT 0,
  `timezone` varchar(255) NOT NULL DEFAULT 'UTC',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `last_login_ip` varchar(255) DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_plan_id_foreign` (`plan_id`),
  CONSTRAINT `users_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: accounts
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('gmail','whatsapp') NOT NULL,
  `identifier` varchar(255) NOT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `access_token` text DEFAULT NULL,
  `refresh_token` text DEFAULT NULL,
  `token_expires_at` timestamp NULL DEFAULT NULL,
  `whatsapp_session_id` varchar(255) DEFAULT NULL,
  `whatsapp_status` enum('disconnected','qr_ready','connected','reconnecting') NOT NULL DEFAULT 'disconnected',
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `last_sync_cursor` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sync_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_user_type_identifier_unique` (`user_id`,`type`,`identifier`),
  KEY `accounts_user_id_foreign` (`user_id`),
  CONSTRAINT `accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: conversations
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `conversations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `account_id` bigint(20) UNSIGNED NOT NULL,
  `source` enum('gmail','whatsapp') NOT NULL,
  `external_id` varchar(255) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `contact_name` varchar(255) DEFAULT NULL,
  `contact_identifier` varchar(255) NOT NULL,
  `contact_avatar` varchar(255) DEFAULT NULL,
  `snippet` text DEFAULT NULL,
  `message_count` int(11) NOT NULL DEFAULT 0,
  `unread_count` int(11) NOT NULL DEFAULT 0,
  `has_pending_reply` tinyint(1) NOT NULL DEFAULT 0,
  `has_commitment` tinyint(1) NOT NULL DEFAULT 0,
  `has_followup` tinyint(1) NOT NULL DEFAULT 0,
  `is_important` tinyint(1) NOT NULL DEFAULT 0,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `last_message_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `conversations_account_external_unique` (`account_id`,`external_id`),
  KEY `conversations_user_source_last_message` (`user_id`,`source`,`last_message_at`),
  KEY `conversations_user_pending_reply` (`user_id`,`has_pending_reply`),
  KEY `conversations_user_commitment` (`user_id`,`has_commitment`),
  KEY `conversations_account_id_foreign` (`account_id`),
  CONSTRAINT `conversations_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: messages
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `account_id` bigint(20) UNSIGNED NOT NULL,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `source` enum('gmail','whatsapp') NOT NULL,
  `external_id` varchar(255) NOT NULL,
  `from_identifier` varchar(255) NOT NULL,
  `from_name` varchar(255) DEFAULT NULL,
  `to_identifier` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `body_raw` longtext DEFAULT NULL,
  `body_plain` text DEFAULT NULL,
  `is_outbound` tinyint(1) NOT NULL DEFAULT 0,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `attachments` json DEFAULT NULL,
  `labels` json DEFAULT NULL,
  `sent_at` timestamp NOT NULL,
  `ai_processed` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `messages_external_id_unique` (`external_id`),
  KEY `messages_conversation_sent` (`conversation_id`,`sent_at`),
  KEY `messages_user_ai_processed` (`user_id`,`ai_processed`),
  KEY `messages_user_source_sent` (`user_id`,`source`,`sent_at`),
  KEY `messages_account_id_foreign` (`account_id`),
  CONSTRAINT `messages_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: extracted_data
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `extracted_data` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `message_id` bigint(20) UNSIGNED NOT NULL,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `intent` varchar(255) DEFAULT NULL,
  `entities` json DEFAULT NULL,
  `reply_required` tinyint(1) NOT NULL DEFAULT 0,
  `reply_urgency` enum('low','medium','high','critical') NOT NULL DEFAULT 'low',
  `suggested_reply` text DEFAULT NULL,
  `has_commitment` tinyint(1) NOT NULL DEFAULT 0,
  `has_followup_required` tinyint(1) NOT NULL DEFAULT 0,
  `followup_date` date DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `keywords` json DEFAULT NULL,
  `sentiment` enum('positive','neutral','negative') NOT NULL DEFAULT 'neutral',
  `confidence_score` decimal(3,2) NOT NULL DEFAULT 0.00,
  `claude_model` varchar(255) DEFAULT NULL,
  `tokens_used` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `extracted_data_message_id_unique` (`message_id`),
  KEY `extracted_data_user_reply_required` (`user_id`,`reply_required`),
  KEY `extracted_data_user_followup` (`user_id`,`has_followup_required`),
  KEY `extracted_data_conversation_id_foreign` (`conversation_id`),
  CONSTRAINT `extracted_data_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `extracted_data_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `extracted_data_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: commitments
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `commitments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `message_id` bigint(20) UNSIGNED NOT NULL,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `description` text NOT NULL,
  `made_by` enum('me','them') NOT NULL,
  `contact_name` varchar(255) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','done','overdue','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `commitments_user_status` (`user_id`,`status`),
  KEY `commitments_user_due_date` (`user_id`,`due_date`),
  KEY `commitments_message_id_foreign` (`message_id`),
  KEY `commitments_conversation_id_foreign` (`conversation_id`),
  CONSTRAINT `commitments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `commitments_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `commitments_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: tasks
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `conversation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `message_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('reply','followup','action','reminder') NOT NULL DEFAULT 'action',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` enum('pending','in_progress','done','cancelled') NOT NULL DEFAULT 'pending',
  `due_date` date DEFAULT NULL,
  `due_time` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `ai_generated` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tasks_user_status_due` (`user_id`,`status`,`due_date`),
  KEY `tasks_user_type` (`user_id`,`type`),
  KEY `tasks_conversation_id_foreign` (`conversation_id`),
  KEY `tasks_message_id_foreign` (`message_id`),
  CONSTRAINT `tasks_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tasks_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tasks_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: daily_summaries
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `daily_summaries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `summary_date` date NOT NULL,
  `total_messages` int(11) NOT NULL DEFAULT 0,
  `pending_replies` int(11) NOT NULL DEFAULT 0,
  `commitments_made` int(11) NOT NULL DEFAULT 0,
  `followups_required` int(11) NOT NULL DEFAULT 0,
  `ai_summary` text DEFAULT NULL,
  `highlights` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_summaries_date_unique` (`summary_date`),
  KEY `daily_summaries_user_date` (`user_id`,`summary_date`),
  CONSTRAINT `daily_summaries_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: jobs (Laravel queue — database driver)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: failed_jobs
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: password_reset_tokens
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: cache
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Table: sessions
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Laravel migrations tracking table
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Seed: Default Plans
-- ─────────────────────────────────────────────────────────────
INSERT INTO `plans`
  (`name`,`slug`,`description`,`price_monthly`,`price_yearly`,`max_gmail_accounts`,`max_whatsapp_accounts`,`max_messages_per_month`,`max_ai_extractions_per_month`,`has_smart_search`,`has_daily_summary`,`has_priority_support`,`has_api_access`,`is_active`,`is_featured`,`sort_order`,`created_at`,`updated_at`)
VALUES
  ('Free','free','Perfect for getting started',0.00,0.00,1,1,500,100,1,1,0,0,1,0,1,NOW(),NOW()),
  ('Pro','pro','For power users and teams',29.00,290.00,5,3,10000,2000,1,1,1,1,0,1,2,NOW(),NOW());

-- ─────────────────────────────────────────────────────────────
-- Seed: migrations table (so Laravel knows schema is applied)
-- ─────────────────────────────────────────────────────────────
INSERT INTO `migrations` (`migration`, `batch`) VALUES
  ('2024_01_01_000001_create_plans_table', 1),
  ('2024_01_01_000002_create_users_table', 1),
  ('2024_01_01_000003_create_accounts_table', 1),
  ('2024_01_01_000004_create_conversations_table', 1),
  ('2024_01_01_000005_create_messages_table', 1),
  ('2024_01_01_000006_create_extracted_data_table', 1),
  ('2024_01_01_000007_create_commitments_table', 1),
  ('2024_01_01_000008_create_tasks_table', 1),
  ('2024_01_01_000009_create_supporting_tables', 1);

SET FOREIGN_KEY_CHECKS = 1;