-- BBA V2 migration — minimal, additive only
-- 1) Add status + stage to projects (ignore if already exists)
ALTER TABLE projects ADD COLUMN IF NOT EXISTS status ENUM('lead','active','archive') DEFAULT 'active';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS stage INT DEFAULT 1;
UPDATE projects SET status='active' WHERE status IS NULL;

-- 2) Practice settings (for job numbering + email modes)
CREATE TABLE IF NOT EXISTS practice_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practice_id INT NOT NULL,
  job_prefix VARCHAR(10) NOT NULL DEFAULT 'CZ',
  next_job_seq INT NOT NULL DEFAULT 125,
  email_mode ENUM('mailto','relay','smtp') NOT NULL DEFAULT 'mailto',
  relay_address VARCHAR(255) DEFAULT NULL,
  smtp_host VARCHAR(255) DEFAULT NULL,
  smtp_user VARCHAR(255) DEFAULT NULL,
  smtp_pass VARCHAR(255) DEFAULT NULL,
  smtp_from VARCHAR(255) DEFAULT NULL,
  UNIQUE KEY (practice_id)
) ENGINE=InnoDB;

INSERT INTO practice_settings (practice_id, job_prefix, next_job_seq)
SELECT 1, 'CZ', 125
WHERE NOT EXISTS (SELECT 1 FROM practice_settings WHERE practice_id=1);

-- 3) Leads + supporting tables (optional, for later steps; harmless if unused)
CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practice_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(64),
  address TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(practice_id), INDEX(email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practice_id INT NOT NULL,
  client_id INT NULL,
  title VARCHAR(255) NOT NULL,
  status ENUM('unopened','opened','draft','editing','sent','withdrawn','expired','declined','accepted') NOT NULL DEFAULT 'draft',
  budget DECIMAL(12,2) NULL,
  pi_level VARCHAR(32) NULL,
  site_address TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(practice_id), INDEX(client_id), INDEX(status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lead_docs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  doc_type ENUM('proposal','appointment','other') NOT NULL,
  file_path VARCHAR(512) NULL,
  status ENUM('draft','sent','opened','accepted','declined') DEFAULT 'draft',
  sent_at DATETIME NULL,
  opened_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(lead_id), INDEX(doc_type), INDEX(status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS portal_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NULL,
  project_id INT NULL,
  token CHAR(48) NOT NULL UNIQUE,
  expires_at DATETIME NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(lead_id), INDEX(project_id)
) ENGINE=InnoDB;