-- AOSWARE CRM Staff Portal — Phase 1 + Phase 2 schema
-- Database: crm

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS tenants (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(150) NOT NULL,
  industry_code VARCHAR(40) NOT NULL DEFAULT 'distribution',
  smart_tips_on TINYINT(1) NOT NULL DEFAULT 1,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tenants_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  phone VARCHAR(40) NULL,
  avatar_path VARCHAR(255) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tenant_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_one_company (user_id),
  KEY idx_tu_user (user_id),
  CONSTRAINT fk_tu_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_tu_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  role_id TINYINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_role (tenant_id, user_id, role_id),
  KEY idx_ur_user (user_id),
  CONSTRAINT fk_ur_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS territories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_terr_tenant (tenant_id),
  CONSTRAINT fk_terr_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS territory_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  territory_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_terr_user (territory_id, user_id),
  KEY idx_tu2_tenant (tenant_id),
  CONSTRAINT fk_tu2_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_tu2_terr FOREIGN KEY (territory_id) REFERENCES territories (id),
  CONSTRAINT fk_tu2_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(40) NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  probability TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_won TINYINT(1) NOT NULL DEFAULT 0,
  is_lost TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ps_tenant (tenant_id, sort_order),
  CONSTRAINT fk_ps_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(190) NULL,
  website VARCHAR(190) NULL,
  industry VARCHAR(80) NULL,
  territory_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  address_line VARCHAR(180) NULL,
  city VARCHAR(80) NULL,
  state VARCHAR(80) NULL,
  postal_code VARCHAR(20) NULL,
  country VARCHAR(80) NULL DEFAULT 'USA',
  next_action VARCHAR(180) NULL,
  next_action_at DATETIME NULL,
  next_visit_at DATETIME NULL,
  visit_subject VARCHAR(180) NULL,
  visit_detail TEXT NULL,
  last_visit_at DATETIME NULL,
  service_assignee_id INT UNSIGNED NULL,
  attrs JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_acc_tenant_name (tenant_id, name),
  KEY idx_acc_owner (owner_id),
  KEY idx_acc_email (tenant_id, email),
  KEY idx_acc_phone (tenant_id, phone),
  KEY idx_acc_visit (tenant_id, next_visit_at),
  CONSTRAINT fk_acc_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_acc_owner FOREIGN KEY (owner_id) REFERENCES users (id),
  CONSTRAINT fk_acc_terr FOREIGN KEY (territory_id) REFERENCES territories (id),
  CONSTRAINT fk_acc_assignee FOREIGN KEY (service_assignee_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  title VARCHAR(120) NULL,
  role_label VARCHAR(80) NULL,
  owner_id INT UNSIGNED NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  email_status ENUM('ok','bounced') NOT NULL DEFAULT 'ok',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_con_tenant (tenant_id),
  KEY idx_con_account (account_id),
  KEY idx_con_email (tenant_id, email),
  KEY idx_con_phone (tenant_id, phone),
  CONSTRAINT fk_con_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_con_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_con_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leads (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL DEFAULT '',
  company VARCHAR(180) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  source VARCHAR(80) NULL,
  status ENUM('new','qualified','unqualified','converted') NOT NULL DEFAULT 'new',
  owner_id INT UNSIGNED NULL,
  territory_id INT UNSIGNED NULL,
  notes TEXT NULL,
  attrs JSON NULL,
  converted_account_id INT UNSIGNED NULL,
  converted_opportunity_id INT UNSIGNED NULL,
  converted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_lead_tenant_status (tenant_id, status),
  KEY idx_lead_owner (owner_id),
  KEY idx_lead_email (tenant_id, email),
  KEY idx_lead_phone (tenant_id, phone),
  CONSTRAINT fk_lead_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_lead_owner FOREIGN KEY (owner_id) REFERENCES users (id),
  CONSTRAINT fk_lead_terr FOREIGN KEY (territory_id) REFERENCES territories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunities (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  stage_id INT UNSIGNED NOT NULL,
  probability TINYINT UNSIGNED NOT NULL DEFAULT 0,
  owner_id INT UNSIGNED NOT NULL,
  next_step VARCHAR(180) NULL,
  next_step_at DATETIME NULL,
  close_date DATE NULL,
  win_loss_reason VARCHAR(180) NULL,
  attrs JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_opp_tenant_stage (tenant_id, stage_id),
  KEY idx_opp_owner (owner_id),
  KEY idx_opp_account (account_id),
  CONSTRAINT fk_opp_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_opp_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_opp_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages (id),
  CONSTRAINT fk_opp_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tasks (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  due_at DATETIME NULL,
  completed_at DATETIME NULL,
  status ENUM('open','completed','cancelled') NOT NULL DEFAULT 'open',
  priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
  owner_id INT UNSIGNED NOT NULL,
  related_type VARCHAR(40) NULL,
  related_id INT UNSIGNED NULL,
  created_by INT UNSIGNED NOT NULL,
  file_name VARCHAR(180) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_task_tenant_owner (tenant_id, owner_id, status),
  KEY idx_task_due (tenant_id, due_at),
  KEY idx_task_related (related_type, related_id),
  CONSTRAINT fk_task_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_task_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activities (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  type ENUM('call','email','meeting','visit','note','task','stage','campaign') NOT NULL,
  subject VARCHAR(180) NOT NULL,
  body TEXT NULL,
  related_type VARCHAR(40) NULL,
  related_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  file_name VARCHAR(180) NULL,
  activity_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_act_tenant_rel (tenant_id, related_type, related_id),
  KEY idx_act_owner (owner_id, activity_at),
  CONSTRAINT fk_act_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_act_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meetings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL,
  location VARCHAR(180) NULL,
  meeting_type ENUM('call','meeting','visit','other') NOT NULL DEFAULT 'meeting',
  related_type VARCHAR(40) NULL,
  related_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_mtg_tenant_start (tenant_id, starts_at),
  KEY idx_mtg_owner (owner_id),
  CONSTRAINT fk_mtg_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_mtg_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS emails (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  related_type VARCHAR(40) NULL,
  related_id INT UNSIGNED NULL,
  direction ENUM('out','in') NOT NULL DEFAULT 'out',
  subject VARCHAR(180) NOT NULL,
  body TEXT NULL,
  to_email VARCHAR(190) NOT NULL,
  from_email VARCHAR(190) NULL,
  owner_id INT UNSIGNED NOT NULL,
  provider VARCHAR(40) NULL,
  sent_at DATETIME NOT NULL,
  status ENUM('logged','sent','failed') NOT NULL DEFAULT 'logged',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_em_tenant (tenant_id, sent_at),
  KEY idx_em_related (related_type, related_id),
  CONSTRAINT fk_em_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_em_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integrations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  provider ENUM('outlook','gmail') NOT NULL,
  kind ENUM('email','calendar') NOT NULL,
  status ENUM('connected','disconnected') NOT NULL DEFAULT 'disconnected',
  connected_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_integration (tenant_id, user_id, provider, kind),
  CONSTRAINT fk_int_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_int_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS assignment_cursors (
  tenant_id INT UNSIGNED NOT NULL,
  last_user_id INT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (tenant_id),
  CONSTRAINT fk_ac_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  succeeded TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_la_email_time (email, attempted_at),
  KEY idx_la_ip_time (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  body VARCHAR(255) NULL,
  url VARCHAR(255) NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ntf_user (tenant_id, user_id, is_read),
  CONSTRAINT fk_ntf_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_ntf_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(40) NULL,
  entity_id INT UNSIGNED NULL,
  ip VARCHAR(45) NULL,
  meta JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_aud_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_connections (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  provider ENUM('mailchimp','constant_contact','outlook','gmail') NOT NULL,
  status ENUM('connected','disconnected') NOT NULL DEFAULT 'disconnected',
  connected_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mkt_conn (tenant_id, provider),
  CONSTRAINT fk_mc_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_templates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  subject VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_tpl_tenant (tenant_id),
  CONSTRAINT fk_tpl_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_tpl_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS marketing_lists (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  filters JSON NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_list_tenant (tenant_id),
  CONSTRAINT fk_list_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_list_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  template_id INT UNSIGNED NULL,
  list_id INT UNSIGNED NULL,
  subject VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  status ENUM('draft','sent') NOT NULL DEFAULT 'draft',
  provider VARCHAR(40) NULL,
  sent_at DATETIME NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_camp_tenant (tenant_id, status),
  CONSTRAINT fk_camp_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_camp_tpl FOREIGN KEY (template_id) REFERENCES email_templates (id),
  CONSTRAINT fk_camp_list FOREIGN KEY (list_id) REFERENCES marketing_lists (id),
  CONSTRAINT fk_camp_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_recipients (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  campaign_id INT UNSIGNED NOT NULL,
  contact_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  token CHAR(64) NOT NULL,
  status ENUM('queued','sent','bounced','unsubscribed','skipped') NOT NULL DEFAULT 'queued',
  click_url VARCHAR(500) NULL,
  sent_at DATETIME NULL,
  opened_at DATETIME NULL,
  clicked_at DATETIME NULL,
  bounced_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cr_token (token),
  KEY idx_cr_campaign (campaign_id, status),
  KEY idx_cr_account (tenant_id, account_id),
  KEY idx_cr_email (tenant_id, email),
  CONSTRAINT fk_cr_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_cr_camp FOREIGN KEY (campaign_id) REFERENCES campaigns (id),
  CONSTRAINT fk_cr_contact FOREIGN KEY (contact_id) REFERENCES contacts (id),
  CONSTRAINT fk_cr_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unsubscribes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'campaign',
  campaign_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_unsub_email (tenant_id, email),
  CONSTRAINT fk_unsub_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS setup_states (
  tenant_id INT UNSIGNED NOT NULL,
  draft JSON NOT NULL,
  published JSON NULL,
  previous JSON NULL,
  draft_updated_at DATETIME NULL,
  published_at DATETIME NULL,
  published_by INT UNSIGNED NULL,
  PRIMARY KEY (tenant_id),
  CONSTRAINT fk_setup_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_setup_user FOREIGN KEY (published_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Phase 4 — staff commerce + customer portal (same database, separate login)

CREATE TABLE IF NOT EXISTS tenant_commerce (
  tenant_id INT UNSIGNED NOT NULL,
  customer_portal_on TINYINT(1) NOT NULL DEFAULT 0,
  customer_pages JSON NULL,
  systems JSON NULL,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (tenant_id),
  CONSTRAINT fk_tcom_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  pages JSON NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cu_email (email),
  KEY idx_cu_account (tenant_id, account_id),
  CONSTRAINT fk_cu_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_cu_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quotes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  opportunity_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  number VARCHAR(40) NOT NULL,
  title VARCHAR(180) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('draft','pending_approval','approved','rejected','sent','signed','void') NOT NULL DEFAULT 'draft',
  body TEXT NULL,
  file_name VARCHAR(180) NULL,
  sent_at DATETIME NULL,
  approved_at DATETIME NULL,
  approved_by INT UNSIGNED NULL,
  reject_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_quote_number (tenant_id, number),
  KEY idx_quote_account (tenant_id, account_id),
  KEY idx_quote_status (tenant_id, status),
  KEY idx_quote_opp (opportunity_id),
  CONSTRAINT fk_quote_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_quote_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_quote_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities (id),
  CONSTRAINT fk_quote_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  opportunity_id INT UNSIGNED NULL,
  quote_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  number VARCHAR(40) NOT NULL,
  title VARCHAR(180) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('open','fulfilled','cancelled') NOT NULL DEFAULT 'open',
  ordered_at DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_order_number (tenant_id, number),
  KEY idx_order_account (tenant_id, account_id, ordered_at),
  KEY idx_order_opp (opportunity_id),
  CONSTRAINT fk_ord_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_ord_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_ord_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities (id),
  CONSTRAINT fk_ord_quote FOREIGN KEY (quote_id) REFERENCES quotes (id),
  CONSTRAINT fk_ord_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contracts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  opportunity_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  number VARCHAR(40) NOT NULL,
  title VARCHAR(180) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  start_date DATE NULL,
  end_date DATE NULL,
  renewal_date DATE NULL,
  status ENUM('draft','active','expired','cancelled') NOT NULL DEFAULT 'draft',
  file_name VARCHAR(180) NULL,
  renewed_from_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ctr_number (tenant_id, number),
  KEY idx_ctr_account (tenant_id, account_id),
  KEY idx_ctr_renewal (tenant_id, renewal_date),
  KEY idx_ctr_renewed_from (tenant_id, renewed_from_id),
  CONSTRAINT fk_ctr_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_ctr_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_ctr_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities (id),
  CONSTRAINT fk_ctr_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NULL,
  owner_id INT UNSIGNED NOT NULL,
  number VARCHAR(40) NOT NULL,
  title VARCHAR(180) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid','void') NOT NULL DEFAULT 'unpaid',
  file_name VARCHAR(180) NULL,
  due_date DATE NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_number (tenant_id, number),
  KEY idx_inv_account (tenant_id, account_id, status),
  CONSTRAINT fk_inv_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_inv_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_inv_order FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT fk_inv_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS portal_documents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  owner_id INT UNSIGNED NOT NULL,
  related_type VARCHAR(40) NULL,
  related_id INT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  kind ENUM('quote','contract','file','esign') NOT NULL DEFAULT 'file',
  body TEXT NULL,
  file_name VARCHAR(180) NULL,
  shared TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_doc_account (tenant_id, account_id, shared),
  CONSTRAINT fk_doc_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_doc_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_doc_owner FOREIGN KEY (owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS signatures (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  document_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  status ENUM('pending','signed','declined') NOT NULL DEFAULT 'pending',
  signer_name VARCHAR(160) NULL,
  signed_file_name VARCHAR(180) NULL,
  signed_at DATETIME NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sig_doc (document_id, status),
  KEY idx_sig_account (tenant_id, account_id),
  CONSTRAINT fk_sig_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_sig_doc FOREIGN KEY (document_id) REFERENCES portal_documents (id),
  CONSTRAINT fk_sig_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  opportunity_id INT UNSIGNED NULL,
  assignee_id INT UNSIGNED NULL,
  number VARCHAR(40) NOT NULL,
  subject VARCHAR(180) NOT NULL,
  body TEXT NULL,
  status ENUM('open','in_progress','done','cancelled') NOT NULL DEFAULT 'open',
  source ENUM('staff','customer','deal_won') NOT NULL DEFAULT 'staff',
  visit_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_number (tenant_id, number),
  KEY idx_job_account (tenant_id, account_id, status),
  KEY idx_job_assignee (assignee_id, status),
  CONSTRAINT fk_job_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_job_account FOREIGN KEY (account_id) REFERENCES accounts (id),
  CONSTRAINT fk_job_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities (id),
  CONSTRAINT fk_job_assignee FOREIGN KEY (assignee_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_notes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  job_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  customer_user_id INT UNSIGNED NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_jn_job (job_id),
  CONSTRAINT fk_jn_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_jn_job FOREIGN KEY (job_id) REFERENCES service_jobs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS equipment (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  model VARCHAR(120) NULL,
  serial_no VARCHAR(120) NULL,
  location VARCHAR(160) NULL,
  status ENUM('active','retired') NOT NULL DEFAULT 'active',
  installed_at DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_eq_account (tenant_id, account_id),
  CONSTRAINT fk_eq_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
  CONSTRAINT fk_eq_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
