-- Sale pipelines (Web Settings) + campaign contact stages + leads

CREATE TABLE IF NOT EXISTS sale_pipelines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(128) NOT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_sale_pipelines_slug (slug)
);

CREATE TABLE IF NOT EXISTS sale_pipeline_stages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pipeline_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_replied TINYINT(1) NOT NULL DEFAULT 0,
  is_end TINYINT(1) NOT NULL DEFAULT 0,
  is_system 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,
  KEY idx_sps_pipeline (pipeline_id),
  CONSTRAINT fk_sps_pipeline FOREIGN KEY (pipeline_id) REFERENCES sale_pipelines(id)
);

CREATE TABLE IF NOT EXISTS campaign_pipeline_contacts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  recipient_id BIGINT NULL,
  conversation_id BIGINT NULL,
  contact_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  contact_name VARCHAR(200) NULL,
  pipeline_id INT NOT NULL,
  stage_id INT NOT NULL,
  lead_id BIGINT NULL,
  entered_stage_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cpc_campaign_email (campaign_id, contact_email),
  INDEX idx_cpc_campaign_stage (campaign_id, stage_id),
  CONSTRAINT fk_cpc_campaign FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cpc_recipient FOREIGN KEY (recipient_id) REFERENCES email_campaign_recipients(id) ON DELETE SET NULL,
  CONSTRAINT fk_cpc_pipeline FOREIGN KEY (pipeline_id) REFERENCES sale_pipelines(id),
  CONSTRAINT fk_cpc_stage FOREIGN KEY (stage_id) REFERENCES sale_pipeline_stages(id)
);

CREATE TABLE IF NOT EXISTS sale_leads (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  pipeline_id INT NOT NULL,
  end_stage_id INT NOT NULL,
  recipient_id BIGINT NULL,
  conversation_id BIGINT NULL,
  contact_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  contact_name VARCHAR(200) NULL,
  converted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_leads_campaign (campaign_id),
  INDEX idx_leads_email (contact_email),
  CONSTRAINT fk_lead_campaign FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_lead_pipeline FOREIGN KEY (pipeline_id) REFERENCES sale_pipelines(id),
  CONSTRAINT fk_lead_stage FOREIGN KEY (end_stage_id) REFERENCES sale_pipeline_stages(id)
);

ALTER TABLE email_campaigns
  ADD COLUMN pipeline_id INT NULL AFTER smtp_account_id,
  ADD CONSTRAINT fk_campaign_pipeline FOREIGN KEY (pipeline_id) REFERENCES sale_pipelines(id) ON DELETE SET NULL;

INSERT INTO sale_pipelines (name, slug, status, is_default, is_system, sort_order)
SELECT 'Default Sales Pipeline', 'default-sales', 'active', 1, 1, 1
WHERE NOT EXISTS (SELECT 1 FROM sale_pipelines WHERE slug = 'default-sales');

INSERT INTO sale_pipeline_stages (pipeline_id, name, sort_order, is_replied, is_end, is_system)
SELECT p.id, 'New Contact', 1, 0, 0, 1
FROM sale_pipelines p WHERE p.slug = 'default-sales'
  AND NOT EXISTS (SELECT 1 FROM sale_pipeline_stages s WHERE s.pipeline_id = p.id AND s.sort_order = 1);

INSERT INTO sale_pipeline_stages (pipeline_id, name, sort_order, is_replied, is_end, is_system)
SELECT p.id, 'Replied', 2, 1, 0, 0
FROM sale_pipelines p WHERE p.slug = 'default-sales'
  AND NOT EXISTS (SELECT 1 FROM sale_pipeline_stages s WHERE s.pipeline_id = p.id AND s.is_replied = 1);

INSERT INTO sale_pipeline_stages (pipeline_id, name, sort_order, is_replied, is_end, is_system)
SELECT p.id, 'Qualified Lead', 3, 0, 1, 0
FROM sale_pipelines p WHERE p.slug = 'default-sales'
  AND NOT EXISTS (SELECT 1 FROM sale_pipeline_stages s WHERE s.pipeline_id = p.id AND s.is_end = 1);

UPDATE email_campaigns SET pipeline_id = (
  SELECT id FROM sale_pipelines WHERE slug = 'default-sales' LIMIT 1
) WHERE pipeline_id IS NULL;