-- Phase 10 — Purchase Orders
-- Run AFTER 009_estimates.sql
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS purchase_orders (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED  NOT NULL,
  vendor_id      INT UNSIGNED  NOT NULL,
  po_number      VARCHAR(50)   NOT NULL,
  issue_date     DATE          NOT NULL,
  expected_date  DATE          DEFAULT NULL,
  status         ENUM('draft','sent','received','partial','closed','cancelled') DEFAULT 'draft',
  subtotal       DECIMAL(15,2) DEFAULT 0.00,
  total          DECIMAL(15,2) DEFAULT 0.00,
  notes          TEXT          DEFAULT NULL,
  bill_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_po_number (tenant_id, po_number),
  INDEX idx_tenant (tenant_id),
  INDEX idx_vendor (vendor_id),
  INDEX idx_status (tenant_id, status),
  CONSTRAINT fk_po_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_po_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_order_lines (
  id           INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  po_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,
  received_qty DECIMAL(15,4) DEFAULT 0.0000,
  created_at   DATETIME      DEFAULT NOW(),
  INDEX idx_po (po_id),
  CONSTRAINT fk_pol_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
