-- ============================================================
-- Migration 004: Proposal / Quote Builder
-- ============================================================

CREATE TABLE IF NOT EXISTS proposals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  client_name VARCHAR(255) NOT NULL,
  client_email VARCHAR(255) NOT NULL,
  client_company VARCHAR(255),
  title VARCHAR(255) NOT NULL,
  scope TEXT NOT NULL,
  deliverables TEXT,
  timeline VARCHAR(255),
  total_amount DECIMAL(10,2),
  payment_terms VARCHAR(500),
  validity_days INT DEFAULT 30,
  notes TEXT,
  status ENUM('draft','sent','viewed','accepted','declined','expired','converted') DEFAULT 'draft',
  token VARCHAR(100) NOT NULL UNIQUE,
  token_expires DATETIME,
  sent_at DATETIME,
  viewed_at DATETIME,
  responded_at DATETIME,
  response_notes TEXT,
  agreement_id INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (agreement_id) REFERENCES agreements(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_token (token),
  INDEX idx_org (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
