-- Phase 9 — Estimates / Quotes
-- Run AFTER 008_api_keys.sql
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS estimates (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED  NOT NULL,
  customer_id      INT UNSIGNED  NOT NULL,
  estimate_number  VARCHAR(50)   NOT NULL,
  issue_date       DATE          NOT NULL,
  expiry_date      DATE          NOT NULL,
  status           ENUM('draft','sent','accepted','declined','converted') DEFAULT 'draft',
  subtotal         DECIMAL(15,2) DEFAULT 0.00,
  tax              DECIMAL(15,2) DEFAULT 0.00,
  total            DECIMAL(15,2) DEFAULT 0.00,
  notes            TEXT          DEFAULT NULL,
  invoice_id       INT UNSIGNED  DEFAULT NULL,
  is_deleted       TINYINT(1)    DEFAULT 0,
  created_at       DATETIME      DEFAULT NOW(),
  updated_at       DATETIME      ON UPDATE NOW(),
  UNIQUE KEY uq_estimate_number (tenant_id, estimate_number),
  INDEX idx_tenant   (tenant_id),
  INDEX idx_customer (customer_id),
  INDEX idx_status   (tenant_id, status),
  CONSTRAINT fk_est_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_est_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS estimate_lines (
  id           INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  estimate_id  INT UNSIGNED  NOT NULL,
  description  VARCHAR(500)  NOT NULL,
  quantity     DECIMAL(15,4) DEFAULT 1.0000,
  unit_price   DECIMAL(15,2) NOT NULL,
  amount       DECIMAL(15,2) NOT NULL,
  created_at   DATETIME      DEFAULT NOW(),
  INDEX idx_estimate (estimate_id),
  CONSTRAINT fk_el_estimate FOREIGN KEY (estimate_id) REFERENCES estimates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
