-- ============================================================
-- CRM Platform - Complete Database Schema
-- MariaDB 10.6+
-- ============================================================

CREATE DATABASE IF NOT EXISTS crm_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE crm_db;


-- ============================================================
-- USERS & AUTH
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email           VARCHAR(255) NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  role            ENUM('admin','manager','staff','sales_rep','support_rep','readonly') NOT NULL DEFAULT 'staff',
  avatar_url      VARCHAR(500),
  phone           VARCHAR(50),
  department      VARCHAR(100),
  title           VARCHAR(100),
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  totp_secret     VARCHAR(64) NULL,
  totp_enabled    TINYINT(1) NOT NULL DEFAULT 0,
  reset_password_token_hash CHAR(64) NULL,
  reset_password_expires_at DATETIME NULL,
  beezifi_sub     VARCHAR(255) DEFAULT NULL,
  last_login      DATETIME,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_reset_password_expires_at (reset_password_expires_at),
  UNIQUE KEY uq_beezifi_sub (beezifi_sub)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS beezifi_oauth_states (
  state      VARCHAR(64)  NOT NULL PRIMARY KEY,
  nonce      VARCHAR(64)  NOT NULL,
  flow       VARCHAR(10)  NOT NULL DEFAULT 'login',
  user_id    INT UNSIGNED DEFAULT NULL,
  expires_at DATETIME     NOT NULL,
  INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB;

-- ============================================================
-- COMPANIES / ACCOUNTS
-- ============================================================
CREATE TABLE IF NOT EXISTS companies (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(255) NOT NULL,
  domain          VARCHAR(255),
  industry        VARCHAR(100),
  company_size    ENUM('1-10','11-50','51-200','201-500','501-1000','1001-5000','5000+'),
  annual_revenue  DECIMAL(15,2),
  phone           VARCHAR(50),
  email           VARCHAR(255),
  website         VARCHAR(500),
  address         VARCHAR(255),
  city            VARCHAR(100),
  state           VARCHAR(100),
  country         VARCHAR(100),
  zip             VARCHAR(20),
  description     TEXT,
  owner_id        INT UNSIGNED,
  status          ENUM('prospect','customer','partner','vendor','churned','inactive') NOT NULL DEFAULT 'prospect',
  type            ENUM('prospect','customer','partner','vendor','reseller','other') NOT NULL DEFAULT 'prospect',
  linkedin_url    VARCHAR(500),
  twitter_handle  VARCHAR(100),
  logo_url        VARCHAR(500),
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_name (name),
  INDEX idx_owner (owner_id),
  INDEX idx_status (status)
) ENGINE=InnoDB;

-- ============================================================
-- CONTACTS
-- ============================================================
CREATE TABLE IF NOT EXISTS contacts (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name        VARCHAR(100) NOT NULL,
  last_name         VARCHAR(100) NOT NULL,
  email             VARCHAR(255),
  phone             VARCHAR(50),
  mobile            VARCHAR(50),
  job_title         VARCHAR(150),
  department        VARCHAR(100),
  company_id        INT UNSIGNED,
  owner_id          INT UNSIGNED,
  lifecycle_stage   ENUM('lead','prospect','marketing_qualified','sales_qualified','opportunity','customer','evangelist','churned') NOT NULL DEFAULT 'lead',
  lead_source       ENUM('website','referral','social_media','email_campaign','cold_call','event','partner','paid_ad','organic_search','other'),
  linkedin_url      VARCHAR(500),
  twitter_handle    VARCHAR(100),
  address           VARCHAR(255),
  city              VARCHAR(100),
  state             VARCHAR(100),
  country           VARCHAR(100),
  zip               VARCHAR(20),
  description       TEXT,
  do_not_contact    TINYINT(1) NOT NULL DEFAULT 0,
  is_active         TINYINT(1) NOT NULL DEFAULT 1,
  last_contacted_at DATETIME,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_email (email),
  INDEX idx_company (company_id),
  INDEX idx_owner (owner_id),
  INDEX idx_lifecycle (lifecycle_stage),
  INDEX idx_fullname (first_name, last_name)
) ENGINE=InnoDB;

-- ============================================================
-- LEADS
-- ============================================================
CREATE TABLE IF NOT EXISTS leads (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name            VARCHAR(100) NOT NULL,
  last_name             VARCHAR(100) NOT NULL,
  email                 VARCHAR(255),
  phone                 VARCHAR(50),
  company_name          VARCHAR(255),
  job_title             VARCHAR(150),
  lead_source           ENUM('website','referral','social_media','email_campaign','cold_call','event','partner','paid_ad','organic_search','other'),
  status                ENUM('new','contacted','working','qualified','unqualified','converted') NOT NULL DEFAULT 'new',
  rating                ENUM('hot','warm','cold'),
  owner_id              INT UNSIGNED,
  description           TEXT,
  annual_revenue        DECIMAL(15,2),
  num_employees         INT,
  converted             TINYINT(1) NOT NULL DEFAULT 0,
  converted_contact_id  INT UNSIGNED,
  converted_company_id  INT UNSIGNED,
  converted_deal_id     INT UNSIGNED,
  converted_at          DATETIME,
  is_active             TINYINT(1) NOT NULL DEFAULT 1,
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (converted_contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (converted_company_id) REFERENCES companies(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_owner (owner_id),
  INDEX idx_converted (converted)
) ENGINE=InnoDB;

-- ============================================================
-- PIPELINES & STAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS pipelines (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  type        ENUM('sales','onboarding','recruiting','project','real_estate','partnership','support','custom') NOT NULL DEFAULT 'sales',
  description VARCHAR(500),
  color       VARCHAR(20) NOT NULL DEFAULT '#3b82f6',
  icon        VARCHAR(50) NOT NULL DEFAULT 'briefcase',
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  created_by  INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pipeline_stages (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pipeline_id     INT UNSIGNED NOT NULL,
  name            VARCHAR(255) NOT NULL,
  order_index     INT NOT NULL DEFAULT 0,
  win_probability INT NOT NULL DEFAULT 0,
  color           VARCHAR(20) NOT NULL DEFAULT '#3b82f6',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pipeline_id) REFERENCES pipelines(id) ON DELETE CASCADE,
  INDEX idx_pipeline (pipeline_id)
) ENGINE=InnoDB;

-- ============================================================
-- DEALS / OPPORTUNITIES
-- ============================================================
CREATE TABLE IF NOT EXISTS deals (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  contact_id    INT UNSIGNED,
  company_id    INT UNSIGNED,
  owner_id      INT UNSIGNED,
  pipeline_id   INT UNSIGNED,
  stage_id      INT UNSIGNED,
  value         DECIMAL(15,2) NOT NULL DEFAULT 0,
  currency      VARCHAR(10) NOT NULL DEFAULT 'USD',
  close_date    DATE,
  probability   INT NOT NULL DEFAULT 0,
  status        ENUM('open','won','lost','abandoned') NOT NULL DEFAULT 'open',
  lost_reason   VARCHAR(255),
  description   TEXT,
  lead_source   ENUM('website','referral','social_media','email_campaign','cold_call','event','partner','paid_ad','organic_search','other'),
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  closed_at     DATETIME,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (pipeline_id) REFERENCES pipelines(id) ON DELETE SET NULL,
  FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  INDEX idx_owner (owner_id),
  INDEX idx_stage (stage_id),
  INDEX idx_status (status),
  INDEX idx_close_date (close_date)
) ENGINE=InnoDB;

-- ============================================================
-- BOARD ITEMS (generic records for non-sales pipelines)
-- ============================================================
CREATE TABLE IF NOT EXISTS board_items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pipeline_id INT UNSIGNED NOT NULL,
  stage_id    INT UNSIGNED,
  title       VARCHAR(255) NOT NULL,
  description TEXT,
  value       DECIMAL(15,2),
  priority    ENUM('low','medium','high','urgent') DEFAULT 'medium',
  status      ENUM('active','completed','archived') NOT NULL DEFAULT 'active',
  contact_id  INT UNSIGNED,
  company_id  INT UNSIGNED,
  owner_id    INT UNSIGNED,
  due_date    DATE,
  custom_data JSON,
  created_by  INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (pipeline_id) REFERENCES pipelines(id) ON DELETE CASCADE,
  FOREIGN KEY (stage_id)    REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  FOREIGN KEY (contact_id)  REFERENCES contacts(id)  ON DELETE SET NULL,
  FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id)    REFERENCES users(id)     ON DELETE SET NULL,
  FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL,
  INDEX idx_pipeline (pipeline_id),
  INDEX idx_stage    (stage_id),
  INDEX idx_status   (status)
) ENGINE=InnoDB;

-- ============================================================
-- ACTIVITIES (generic timeline entries)
-- ============================================================
CREATE TABLE IF NOT EXISTS activities (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type              ENUM('call','email','meeting','task','note','sms','linkedin','other') NOT NULL DEFAULT 'other',
  subject           VARCHAR(255) NOT NULL,
  description       TEXT,
  contact_id        INT UNSIGNED,
  company_id        INT UNSIGNED,
  deal_id           INT UNSIGNED,
  lead_id           INT UNSIGNED,
  ticket_id         INT UNSIGNED,
  owner_id          INT UNSIGNED,
  due_date          DATETIME,
  duration_minutes  INT,
  outcome           ENUM('connected','left_voicemail','no_answer','sent','opened','bounced','completed','cancelled','rescheduled'),
  completed         TINYINT(1) NOT NULL DEFAULT 0,
  completed_at      DATETIME,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_type (type),
  INDEX idx_owner (owner_id),
  INDEX idx_contact (contact_id),
  INDEX idx_deal (deal_id),
  INDEX idx_due_date (due_date)
) ENGINE=InnoDB;

-- ============================================================
-- TASKS
-- ============================================================
CREATE TABLE IF NOT EXISTS tasks (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(255) NOT NULL,
  description   TEXT,
  contact_id    INT UNSIGNED,
  company_id    INT UNSIGNED,
  deal_id       INT UNSIGNED,
  lead_id       INT UNSIGNED,
  assigned_to   INT UNSIGNED,
  created_by    INT UNSIGNED,
  due_date      DATETIME,
  priority      ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status        ENUM('todo','in_progress','completed','cancelled') NOT NULL DEFAULT 'todo',
  completed_at  DATETIME,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_assigned (assigned_to),
  INDEX idx_status (status),
  INDEX idx_priority (priority),
  INDEX idx_due_date (due_date)
) ENGINE=InnoDB;

-- ============================================================
-- NOTES
-- ============================================================
CREATE TABLE IF NOT EXISTS notes (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content       TEXT NOT NULL,
  contact_id    INT UNSIGNED,
  company_id    INT UNSIGNED,
  deal_id       INT UNSIGNED,
  lead_id       INT UNSIGNED,
  ticket_id     INT UNSIGNED,
  created_by    INT UNSIGNED,
  is_pinned     TINYINT(1) NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_contact (contact_id),
  INDEX idx_company (company_id),
  INDEX idx_deal (deal_id)
) ENGINE=InnoDB;

-- ============================================================
-- EMAILS (logged)
-- ============================================================
CREATE TABLE IF NOT EXISTS emails (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject       VARCHAR(500) NOT NULL,
  body          TEXT,
  from_email    VARCHAR(255),
  to_email      VARCHAR(255),
  cc            VARCHAR(500),
  bcc           VARCHAR(500),
  contact_id    INT UNSIGNED,
  company_id    INT UNSIGNED,
  deal_id       INT UNSIGNED,
  direction     ENUM('inbound','outbound') NOT NULL DEFAULT 'outbound',
  status        ENUM('draft','sent','received','failed') NOT NULL DEFAULT 'sent',
  sent_at       DATETIME,
  created_by    INT UNSIGNED,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_contact (contact_id),
  INDEX idx_direction (direction)
) ENGINE=InnoDB;

-- ============================================================
-- CALLS (logged)
-- ============================================================
CREATE TABLE IF NOT EXISTS calls (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject           VARCHAR(255) NOT NULL,
  description       TEXT,
  contact_id        INT UNSIGNED,
  company_id        INT UNSIGNED,
  deal_id           INT UNSIGNED,
  direction         ENUM('inbound','outbound') NOT NULL DEFAULT 'outbound',
  duration_seconds  INT,
  outcome           ENUM('connected','left_voicemail','no_answer','busy','wrong_number') NOT NULL DEFAULT 'connected',
  recording_url     VARCHAR(500),
  owner_id          INT UNSIGNED,
  call_at           DATETIME,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_contact (contact_id),
  INDEX idx_owner (owner_id)
) ENGINE=InnoDB;

-- ============================================================
-- MEETINGS (logged)
-- ============================================================
CREATE TABLE IF NOT EXISTS meetings (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(255) NOT NULL,
  description   TEXT,
  location      VARCHAR(255),
  start_time    DATETIME,
  end_time      DATETIME,
  contact_id    INT UNSIGNED,
  company_id    INT UNSIGNED,
  deal_id       INT UNSIGNED,
  owner_id      INT UNSIGNED,
  status        ENUM('scheduled','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  meeting_link  VARCHAR(500),
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_contact (contact_id),
  INDEX idx_owner (owner_id),
  INDEX idx_start_time (start_time)
) ENGINE=InnoDB;

-- ============================================================
-- SUPPORT TICKETS
-- ============================================================
CREATE TABLE IF NOT EXISTS tickets (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_number   VARCHAR(20) NOT NULL UNIQUE,
  subject         VARCHAR(500) NOT NULL,
  description     TEXT,
  contact_id      INT UNSIGNED,
  company_id      INT UNSIGNED,
  assigned_to     INT UNSIGNED,
  created_by      INT UNSIGNED,
  priority        ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status          ENUM('open','pending','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  type            ENUM('bug','feature_request','question','billing','other') NOT NULL DEFAULT 'question',
  resolution      TEXT,
  resolved_at     DATETIME,
  closed_at       DATETIME,
  first_response_at DATETIME,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_priority (priority),
  INDEX idx_assigned (assigned_to),
  INDEX idx_contact (contact_id),
  INDEX idx_number (ticket_number)
) ENGINE=InnoDB;

-- ============================================================
-- TAGS
-- ============================================================
CREATE TABLE IF NOT EXISTS tags (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  color       VARCHAR(20) NOT NULL DEFAULT '#3b82f6',
  entity_type ENUM('contact','company','deal','lead','ticket','all') NOT NULL DEFAULT 'all',
  created_by  INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_name_type (name, entity_type)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS entity_tags (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag_id      INT UNSIGNED NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id   INT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  UNIQUE KEY uq_tag_entity (tag_id, entity_type, entity_id),
  INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB;

-- ============================================================
-- CUSTOM FIELDS
-- ============================================================
CREATE TABLE IF NOT EXISTS custom_fields (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type ENUM('contact','company','deal','lead','ticket') NOT NULL,
  field_label VARCHAR(255) NOT NULL,
  field_name  VARCHAR(100) NOT NULL,
  field_type  ENUM('text','number','date','boolean','select','multiselect','url','email','phone') NOT NULL DEFAULT 'text',
  options     JSON,
  required    TINYINT(1) NOT NULL DEFAULT 0,
  order_index INT NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  created_by  INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_field (entity_type, field_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS custom_field_values (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  custom_field_id INT UNSIGNED NOT NULL,
  entity_type     VARCHAR(50) NOT NULL,
  entity_id       INT UNSIGNED NOT NULL,
  value           TEXT,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (custom_field_id) REFERENCES custom_fields(id) ON DELETE CASCADE,
  UNIQUE KEY uq_field_entity (custom_field_id, entity_type, entity_id),
  INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB;

-- ============================================================
-- FILES / ATTACHMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS files (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  original_name VARCHAR(500) NOT NULL,
  stored_name   VARCHAR(500) NOT NULL,
  file_path     VARCHAR(1000) NOT NULL,
  file_size     BIGINT,
  mime_type     VARCHAR(255),
  entity_type   VARCHAR(50),
  entity_id     INT UNSIGNED,
  uploaded_by   INT UNSIGNED,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB;

-- ============================================================
-- AUDIT LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED,
  action      ENUM('create','update','delete','view','login','logout','export','import','convert') NOT NULL,
  entity_type VARCHAR(50),
  entity_id   INT UNSIGNED,
  entity_name VARCHAR(255),
  old_values  JSON,
  new_values  JSON,
  ip_address  VARCHAR(45),
  user_agent  TEXT,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user (user_id),
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_action (action),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS notifications (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  type        VARCHAR(50) NOT NULL,
  title       VARCHAR(255) NOT NULL,
  message     TEXT,
  entity_type VARCHAR(50),
  entity_id   INT UNSIGNED,
  is_read     TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_unread (user_id, is_read),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- SAVED VIEWS / FILTERS
-- ============================================================
CREATE TABLE IF NOT EXISTS saved_views (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  filters     JSON,
  columns     JSON,
  sort_by     VARCHAR(100),
  sort_order  ENUM('asc','desc') DEFAULT 'asc',
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  is_shared   TINYINT(1) NOT NULL DEFAULT 0,
  created_by  INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_entity_user (entity_type, created_by)
) ENGINE=InnoDB;

-- ============================================================
-- PAYMENTS (purchase history)
-- ============================================================
CREATE TABLE IF NOT EXISTS payments (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stripe_payment_id   VARCHAR(255) NOT NULL,
  plan                ENUM('starter','growth','business','enterprise') NOT NULL,
  billing_period      ENUM('6months','1year') NOT NULL,
  amount_cents        INT UNSIGNED NOT NULL,
  currency            VARCHAR(8) NOT NULL DEFAULT 'usd',
  status              ENUM('succeeded','refunded','failed') NOT NULL DEFAULT 'succeeded',
  paid_by_user_id     INT UNSIGNED,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- SUBSCRIPTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS subscriptions (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan                ENUM('starter','growth','business','enterprise') NOT NULL DEFAULT 'starter',
  status              ENUM('active','trial','cancelled','past_due') NOT NULL DEFAULT 'active',
  max_users           INT UNSIGNED NOT NULL DEFAULT 10,
  price_monthly       DECIMAL(10,2) DEFAULT 20.00,
  billing_cycle       ENUM('monthly','annual') NOT NULL DEFAULT 'annual',
  billing_period      ENUM('6months','1year') DEFAULT NULL,
  paid_until          DATE DEFAULT NULL,
  stripe_payment_id   VARCHAR(255) DEFAULT NULL,
  enterprise_note     TEXT,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- EMAIL SETTINGS (per-tenant SMTP config, password encrypted)
-- ============================================================
CREATE TABLE IF NOT EXISTS email_settings (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  host       VARCHAR(255),
  port       INT UNSIGNED NOT NULL DEFAULT 587,
  secure     TINYINT(1) NOT NULL DEFAULT 0,
  username   VARCHAR(255),
  password   TEXT,
  from_name  VARCHAR(255),
  from_email VARCHAR(255),
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- EMAIL TEMPLATES
-- ============================================================
CREATE TABLE IF NOT EXISTS email_templates (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                 VARCHAR(255) NOT NULL,
  subject              VARCHAR(500) NOT NULL,
  body                 TEXT,
  stage                VARCHAR(100) NULL,
  stage_number         INT NULL,
  sequence_position    INT NULL,
  product              VARCHAR(100) NULL,
  send_trigger         VARCHAR(100) NULL,
  send_delay_days      INT NOT NULL DEFAULT 0,
  target_persona       TEXT NULL,
  subject_alternatives JSON NULL,
  variables            JSON NULL,
  tags                 JSON NULL,
  notes                TEXT NULL,
  created_by           INT UNSIGNED,
  is_active            TINYINT(1) NOT NULL DEFAULT 1,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_active (is_active),
  INDEX idx_stage  (stage, stage_number, sequence_position)
) ENGINE=InnoDB;

-- ============================================================
-- USER EMAIL SETTINGS (per-user SMTP for sending from contact pages)
-- ============================================================
CREATE TABLE IF NOT EXISTS user_email_settings (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL UNIQUE,
  host       VARCHAR(255),
  port       INT UNSIGNED NOT NULL DEFAULT 587,
  secure     TINYINT(1) NOT NULL DEFAULT 0,
  username   VARCHAR(255),
  password   TEXT,
  from_name  VARCHAR(255),
  from_email VARCHAR(255),
  signature  TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- ============================================================
-- API KEYS (developer integrations)
-- ============================================================
CREATE TABLE IF NOT EXISTS api_keys (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,
  key_prefix   VARCHAR(20)  NOT NULL,
  key_hash     VARCHAR(64)  NOT NULL,
  created_by   INT UNSIGNED,
  last_used_at DATETIME NULL,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE INDEX idx_hash (key_hash),
  INDEX idx_active (is_active),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

