-- AP Invoicing Module Tables
CREATE TABLE IF NOT EXISTS ap_invoices (
  invoice_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  vendor_id         BIGINT NOT NULL,
  invoice_number    VARCHAR(64) NOT NULL,
  invoice_date      DATE NOT NULL,
  invoice_total     DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency_code     CHAR(3) NOT NULL DEFAULT 'USD',
  freight_amount    DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  tax_amount        DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  terms_code        VARCHAR(32) NULL,
  status            ENUM('Draft','Matching','NeedsReview','Approved','ReadyForExport','Exported','Voided') NOT NULL DEFAULT 'Draft',
  created_by        VARCHAR(64) NOT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  approved_by       VARCHAR(64) NULL,
  approved_at       DATETIME NULL,
  export_batch_id   VARCHAR(64) NULL,
  exported_at       DATETIME NULL,
  notes             TEXT NULL,
  UNIQUE KEY uk_vendor_inv (vendor_id, invoice_number),
  KEY idx_vendor (vendor_id),
  KEY idx_status (status)
);

CREATE TABLE IF NOT EXISTS ap_invoice_attachments (
  attachment_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_id        BIGINT NOT NULL,
  file_name         VARCHAR(255) NOT NULL,
  file_path         VARCHAR(512) NOT NULL,
  file_size_bytes   BIGINT NOT NULL,
  content_type      VARCHAR(128) NOT NULL,
  uploaded_by       VARCHAR(64) NOT NULL,
  uploaded_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_invoice (invoice_id)
);

CREATE TABLE IF NOT EXISTS ap_invoice_lines (
  invoice_line_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_id        BIGINT NOT NULL,
  line_no           INT NOT NULL,
  po_id             BIGINT NULL,
  po_item_id        BIGINT NULL,
  item_code         VARCHAR(128) NULL,
  description       VARCHAR(512) NOT NULL,
  qty_invoiced      DECIMAL(18,4) NOT NULL DEFAULT 0,
  uom               VARCHAR(16) NULL,
  unit_price        DECIMAL(18,4) NOT NULL DEFAULT 0,
  line_amount       DECIMAL(18,2) NOT NULL DEFAULT 0,
  taxable           TINYINT(1) NOT NULL DEFAULT 0,
  freight_alloc     DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  tax_alloc         DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  status            ENUM('Unmatched','PartiallyMatched','Matched') NOT NULL DEFAULT 'Unmatched',
  KEY idx_invoice (invoice_id),
  KEY idx_po (po_id),
  KEY idx_po_item (po_item_id)
);

CREATE TABLE IF NOT EXISTS ap_matches (
  match_id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_line_id   BIGINT NOT NULL,
  receipt_item_id   BIGINT NOT NULL, -- FK to po_receipt_item.id
  qty_matched       DECIMAL(18,4) NOT NULL,
  unit_price_rcvd   DECIMAL(18,4) NOT NULL,
  price_variance    DECIMAL(18,4) NOT NULL,
  extended_variance DECIMAL(18,2) NOT NULL,
  variance_reason   VARCHAR(256) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_line_receipt (invoice_line_id, receipt_item_id),
  KEY idx_receipt (receipt_item_id)
);

CREATE TABLE IF NOT EXISTS ap_tolerances (
  vendor_id         BIGINT NOT NULL PRIMARY KEY,  -- vendor_id=0 for global defaults
  price_pct         DECIMAL(9,4) NOT NULL DEFAULT 0.0200,
  price_abs         DECIMAL(18,4) NOT NULL DEFAULT 1.0000,
  qty_pct           DECIMAL(9,4) NOT NULL DEFAULT 0.0000,
  qty_abs           DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  active            TINYINT(1) NOT NULL DEFAULT 1,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ap_audit (
  audit_id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  entity_type       ENUM('Invoice','InvoiceLine','Match','Export') NOT NULL,
  entity_id         BIGINT NOT NULL,
  action            VARCHAR(64) NOT NULL,
  actor             VARCHAR(64) NOT NULL,
  message           VARCHAR(1024) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_entity (entity_type, entity_id)
);
