-- Mocombe Financial CRM Database Schema
-- MySQL 8.0+


USE fu0t5f63x4e3i5g3_mocombe_crm;

-- ─── USERS (clients and agents) ───
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('client','agent','admin') NOT NULL DEFAULT 'client',
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  dob DATE,
  ssn_last4 CHAR(4) COMMENT 'Last 4 digits for verification only',
  address_line1 VARCHAR(255),
  address_line2 VARCHAR(100),
  city VARCHAR(100),
  state CHAR(2),
  zip VARCHAR(10),
  avatar_url VARCHAR(500),
  email_verified_at DATETIME,
  last_login_at DATETIME,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_email (email),
  INDEX idx_users_role (role)
) ENGINE=InnoDB;

-- ─── AGENT-CLIENT ASSIGNMENTS ───
CREATE TABLE agent_clients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_agent_client (agent_id, client_id),
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─── CARRIERS ───
CREATE TABLE carriers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) UNIQUE,
  logo_url VARCHAR(500),
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO carriers (name, code) VALUES
('Aetna','AET'),('AllState','ALL'),('Anthem','ANT'),('Blue Cross Blue Shield','BCBS'),
('Cigna','CIG'),('Humana','HUM'),('Kaiser Permanente','KAI'),('MetLife','MET'),
('Mutual of Omaha','MOO'),('Prudential','PRU'),('State Farm','STF'),('UnitedHealthcare','UHC');

-- ─── POLICIES ───
CREATE TABLE policies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  carrier_id INT UNSIGNED,
  policy_number VARCHAR(100) NOT NULL,
  type ENUM('life','health','dental','vision','medicare_advantage','medicare_supplement',
            'medicare_part_d','long_term_care','hospital_indemnity','critical_illness',
            'auto','home','umbrella','commercial','other') NOT NULL,
  status ENUM('active','pending','cancelled','expired','lapsed') NOT NULL DEFAULT 'active',
  effective_date DATE NOT NULL,
  expiration_date DATE,
  premium DECIMAL(10,2) NOT NULL,
  premium_frequency ENUM('monthly','quarterly','semi_annual','annual') NOT NULL DEFAULT 'monthly',
  coverage_amount DECIMAL(14,2) COMMENT 'Face amount for life, max benefit for others',
  deductible DECIMAL(10,2),
  beneficiary TEXT COMMENT 'JSON array of beneficiaries',
  notes TEXT,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL,
  INDEX idx_policies_client (client_id),
  INDEX idx_policies_status (status),
  INDEX idx_policies_type (type),
  INDEX idx_policies_expiration (expiration_date)
) ENGINE=InnoDB;

-- ─── POLICY DOCUMENTS ───
CREATE TABLE documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  policy_id INT UNSIGNED,
  uploaded_by INT UNSIGNED NOT NULL COMMENT 'User who uploaded (agent or client)',
  name VARCHAR(255) NOT NULL,
  type ENUM('policy_declaration','id_card','application','claim_form','riders',
            'correspondence','other') NOT NULL DEFAULT 'other',
  file_path VARCHAR(500) NOT NULL,
  file_size INT UNSIGNED,
  mime_type VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE SET NULL,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_documents_client (client_id),
  INDEX idx_documents_policy (policy_id)
) ENGINE=InnoDB;

-- ─── CLAIMS ───
CREATE TABLE claims (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  policy_id INT UNSIGNED NOT NULL,
  claim_number VARCHAR(100) NOT NULL UNIQUE,
  type ENUM('health','life','dental','vision','accident','hospitalization',
            'critical_illness','disability','property','liability','other') NOT NULL,
  status ENUM('draft','submitted','in_review','approved','denied','pending_docs',
              'paid','closed') NOT NULL DEFAULT 'draft',
  amount_requested DECIMAL(12,2),
  amount_approved DECIMAL(12,2),
  amount_paid DECIMAL(12,2),
  incident_date DATE,
  filed_date DATE,
  decision_date DATE,
  description TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE CASCADE,
  INDEX idx_claims_client (client_id),
  INDEX idx_claims_status (status)
) ENGINE=InnoDB;

-- ─── CLAIM DOCUMENTS ───
CREATE TABLE claim_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  claim_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_size INT UNSIGNED,
  mime_type VARCHAR(100),
  uploaded_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (claim_id) REFERENCES claims(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─── PAYMENTS ───
CREATE TABLE payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  policy_id INT UNSIGNED,
  amount DECIMAL(10,2) NOT NULL,
  payment_date DATE NOT NULL,
  due_date DATE,
  method ENUM('credit_card','ach','check','cash','carrier_direct','other') NOT NULL DEFAULT 'other',
  status ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  transaction_id VARCHAR(255),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE SET NULL,
  INDEX idx_payments_client (client_id),
  INDEX idx_payments_status (status),
  INDEX idx_payments_date (payment_date)
) ENGINE=InnoDB;

-- ─── ACTIVITY LOG ───
CREATE TABLE activity_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  action VARCHAR(100) NOT NULL COMMENT 'e.g. policy_viewed, claim_filed, document_uploaded',
  entity_type VARCHAR(50) COMMENT 'policy, claim, document, payment',
  entity_id INT UNSIGNED,
  details JSON,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_activity_user (user_id),
  INDEX idx_activity_action (action),
  INDEX idx_activity_created (created_at)
) ENGINE=InnoDB;

-- ─── PASSWORD RESET TOKENS ───
CREATE TABLE password_resets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reset_token (token),
  INDEX idx_reset_user (user_id)
) ENGINE=InnoDB;

-- ─── SESSION TOKENS ───
CREATE TABLE sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  refresh_token VARCHAR(255) UNIQUE,
  user_agent VARCHAR(500),
  ip_address VARCHAR(45),
  expires_at DATETIME NOT NULL,
  last_activity DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sessions_token (token),
  INDEX idx_sessions_user (user_id)
) ENGINE=InnoDB;

-- ─── EMAIL CAMPAIGNS ───
CREATE TABLE email_campaigns (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  subject VARCHAR(500) NOT NULL,
  preview_text VARCHAR(300),
  body_html TEXT NOT NULL,
  sender_name VARCHAR(100),
  sender_email VARCHAR(255),
  status ENUM('draft','scheduled','sending','sent','paused','cancelled') NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME,
  sent_at DATETIME,
  recipient_count INT UNSIGNED DEFAULT 0,
  opens INT UNSIGNED DEFAULT 0,
  clicks INT UNSIGNED DEFAULT 0,
  bounces INT UNSIGNED DEFAULT 0,
  unsubscribes INT UNSIGNED DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_email_campaigns_status (status),
  INDEX idx_email_campaigns_scheduled (scheduled_at)
) ENGINE=InnoDB;

-- ─── CAMPAIGN RECIPIENTS ───
CREATE TABLE campaign_recipients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED,
  email VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  sent_at DATETIME,
  opened_at DATETIME,
  clicked_at DATETIME,
  bounced TINYINT(1) DEFAULT 0,
  unsubscribed TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_campaign_recipients_campaign (campaign_id),
  INDEX idx_campaign_recipients_sent (sent_at)
) ENGINE=InnoDB;

-- ─── SOCIAL POSTS ───
CREATE TABLE social_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED NOT NULL,
  platform ENUM('facebook','instagram','linkedin','twitter','tiktok','multiple') NOT NULL DEFAULT 'multiple',
  content TEXT NOT NULL,
  media_urls JSON COMMENT 'Array of image/video URLs',
  link_url VARCHAR(500),
  status ENUM('draft','scheduled','published','failed') NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME,
  published_at DATETIME,
  engagement_likes INT UNSIGNED DEFAULT 0,
  engagement_comments INT UNSIGNED DEFAULT 0,
  engagement_shares INT UNSIGNED DEFAULT 0,
  engagement_clicks INT UNSIGNED DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_social_posts_status (status),
  INDEX idx_social_posts_platform (platform),
  INDEX idx_social_posts_scheduled (scheduled_at)
) ENGINE=InnoDB;

-- ─── SMS CAMPAIGNS ───
CREATE TABLE sms_campaigns (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  message_body TEXT NOT NULL,
  sender_id VARCHAR(30),
  status ENUM('draft','scheduled','sending','sent','paused','cancelled') NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME,
  sent_at DATETIME,
  recipient_count INT UNSIGNED DEFAULT 0,
  delivered_count INT UNSIGNED DEFAULT 0,
  failed_count INT UNSIGNED DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sms_campaigns_status (status),
  INDEX idx_sms_campaigns_scheduled (scheduled_at)
) ENGINE=InnoDB;

-- ─── SMS RECIPIENTS ───
CREATE TABLE sms_recipients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED,
  phone VARCHAR(20) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  sent_at DATETIME,
  delivered_at DATETIME,
  failed TINYINT(1) DEFAULT 0,
  opt_out TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (campaign_id) REFERENCES sms_campaigns(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_sms_recipients_campaign (campaign_id)
) ENGINE=InnoDB;

-- ─── COMPETITOR WEBSITES (monitoring targets) ───
CREATE TABLE competitor_websites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  url VARCHAR(500) NOT NULL,
  description TEXT,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_scraped_at DATETIME,
  scrape_frequency ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'weekly',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_competitor_active (is_active)
) ENGINE=InnoDB;

-- ─── COMPETITOR SCRAPE RESULTS ───
CREATE TABLE competitor_scrapes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  competitor_id INT UNSIGNED NOT NULL,
  scraped_at DATETIME NOT NULL,
  page_url VARCHAR(500) NOT NULL,
  page_title VARCHAR(500),
  meta_description TEXT,
  h1_tags JSON,
  key_headlines JSON,
  cta_text VARCHAR(500),
  cta_href VARCHAR(500),
  offers JSON,
  services_mentioned JSON,
  content_snippets JSON,
  word_count INT UNSIGNED,
  diff_summary TEXT,
  raw_html LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competitor_id) REFERENCES competitor_websites(id) ON DELETE CASCADE,
  INDEX idx_scrapes_competitor (competitor_id),
  INDEX idx_scrapes_date (scraped_at)
) ENGINE=InnoDB;

-- ─── COMPETITOR INSIGHTS / RECOMMENDATIONS ───
CREATE TABLE competitor_insights (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  competitor_id INT UNSIGNED NOT NULL,
  scrape_id INT UNSIGNED,
  insight_type ENUM('pricing','messaging','offer','seo','design','social','product','other') NOT NULL,
  headline VARCHAR(500) NOT NULL,
  detail TEXT,
  recommendation TEXT,
  priority ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  is_actioned TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competitor_id) REFERENCES competitor_websites(id) ON DELETE CASCADE,
  FOREIGN KEY (scrape_id) REFERENCES competitor_scrapes(id) ON DELETE SET NULL,
  INDEX idx_insights_competitor (competitor_id),
  INDEX idx_insights_type (insight_type),
  INDEX idx_insights_priority (priority)
) ENGINE=InnoDB;

-- ─── MARKETING ANALYTICS ───
CREATE TABLE marketing_analytics (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recorded_date DATE NOT NULL,
  channel ENUM('email','social','sms','web','referral','other') NOT NULL,
  metric_name VARCHAR(100) NOT NULL,
  metric_value DECIMAL(14,2) NOT NULL,
  dimension VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_analytics_date (recorded_date, channel, metric_name, dimension),
  INDEX idx_analytics_date (recorded_date),
  INDEX idx_analytics_channel (channel)
) ENGINE=InnoDB;