-- Cybil CRM MySQL 8 schema
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('Super Admin','Staff') NOT NULL,
  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
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  company_name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(50),
  product ENUM('Company Incorporation','Bank Account Opening','VAT Registration','Home Loans','Business Loans','License Renewal') NOT NULL,
  status ENUM('New','Contacted','Qualified','Proposal Sent','Converted','Lost') NOT NULL,
  owner_id INT UNSIGNED NOT NULL,
  source VARCHAR(100),
  preferred_contact_method ENUM('Email','Phone','WhatsApp'),
  value DECIMAL(15,2),
  notes TEXT,
  company_open_date DATE NULL,
  tags_json JSON,
  bank_name VARCHAR(150) NULL,
  license_issued_date DATE NULL,
  expiry_date DATE NULL,
  authority VARCHAR(150) NULL,
  ref_person VARCHAR(150) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_leads_owner (owner_id),
  INDEX idx_leads_status (status),
  INDEX idx_leads_product (product),
  INDEX idx_leads_created (created_at),
  CONSTRAINT fk_leads_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lead_activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  type ENUM('Call','Email','Meeting','Note','File','StatusChange','Assignment') NOT NULL,
  content TEXT,
  activity_date DATETIME NOT NULL,
  user_id INT UNSIGNED NULL,
  user_name VARCHAR(150),
  INDEX idx_activities_lead (lead_id),
  INDEX idx_activities_date (activity_date),
  CONSTRAINT fk_act_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_act_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  trade_license VARCHAR(100) NOT NULL UNIQUE,
  incorporation_date DATE NOT NULL,
  license_issued_date DATE NULL,
  last_renewal_date DATE NULL,
  renewal_date DATE NOT NULL,
  status ENUM('Active','Expired','Suspended') NOT NULL,
  is_loan_eligible TINYINT(1) NOT NULL DEFAULT 0,
  vat_number VARCHAR(100) NULL,
  vat_registration_date DATE NULL,
  bank_name VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  active_products_json JSON,
  linked_lead_id INT NULL,
  authority VARCHAR(150) NULL,
  ref_person VARCHAR(150) NULL,
  owner_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_comp_status (status),
  INDEX idx_comp_renewal (renewal_date),
  INDEX idx_comp_loan (is_loan_eligible),
  INDEX idx_comp_owner (owner_id),
  CONSTRAINT fk_comp_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_comp_lead FOREIGN KEY (linked_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS company_contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  role VARCHAR(100),
  email VARCHAR(255),
  phone VARCHAR(50),
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_contact_company (company_id),
  CONSTRAINT fk_contact_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS company_banks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  bank_name VARCHAR(150) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company_banks_company (company_id),
  CONSTRAINT fk_company_banks_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS company_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  url VARCHAR(500) NOT NULL,
  type VARCHAR(50) NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_doc_company (company_id),
  CONSTRAINT fk_doc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS company_communication_preferences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL UNIQUE,
  email TINYINT(1) NOT NULL DEFAULT 1,
  sms TINYINT(1) NOT NULL DEFAULT 0,
  whatsapp TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_comm_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  due_date DATE,
  priority ENUM('High','Medium','Low') NOT NULL,
  status ENUM('Pending','In Progress','Completed') NOT NULL,
  type VARCHAR(100),
  owner_id INT UNSIGNED NOT NULL,
  related_company_id INT NULL,
  related_lead_id INT NULL,
  related_entity_display VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_task_owner (owner_id),
  INDEX idx_task_status (status),
  INDEX idx_task_priority (priority),
  INDEX idx_task_due (due_date),
  CONSTRAINT fk_task_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_task_company FOREIGN KEY (related_company_id) REFERENCES companies(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_task_lead FOREIGN KEY (related_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  active TINYINT(1) NOT NULL DEFAULT 1,
  checklist_json JSON
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS service_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  service_id INT NOT NULL,
  rule_type VARCHAR(100) NOT NULL,
  trigger_days INT NOT NULL,
  description TEXT,
  INDEX idx_rule_service (service_id),
  CONSTRAINT fk_rule_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT,
  type ENUM('info','success','warning','error') NOT NULL,
  timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  link VARCHAR(255) NULL,
  INDEX idx_notif_user (user_id),
  INDEX idx_notif_type (type),
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notification_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  trigger_event ENUM('Lead Assignment','Renewal Due','Loan Eligibility','VAT Follow-up') NOT NULL,
  channels_json JSON,
  subject_template TEXT,
  body_template TEXT,
  variables_json JSON
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  user_id INT UNSIGNED NULL,
  user_name VARCHAR(150) NOT NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50) NULL,
  entity_id VARCHAR(100) NULL,
  entity_label VARCHAR(255) NULL,
  details TEXT NULL,
  ip_address VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_activity_user (user_id),
  INDEX idx_activity_action (action),
  INDEX idx_activity_created (created_at),
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Single-row config controlling the bulk company import page's visibility window.
-- Seeded once with a 7-day window; a Super Admin can reset it to a fresh 24-hour
-- window at any time via POST /api/import/window/enable.
CREATE TABLE IF NOT EXISTS import_window (
  id TINYINT PRIMARY KEY,
  enabled_until DATETIME NOT NULL,
  updated_by INT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_import_window_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;