-- Non-PO Invoice Support Tables and Migrations
-- Adds support for invoices not tied to purchase orders, with GL coding and approval routing

-- Add invoice_type column to ap_invoices
ALTER TABLE ap_invoices
  ADD COLUMN invoice_type ENUM('po','non_po') NOT NULL DEFAULT 'po' AFTER po_id,
  ADD COLUMN raw_document_path VARCHAR(512) NULL AFTER notes;

-- Table for line item GL code allocations (supports splitting across jobs/cost codes)
CREATE TABLE IF NOT EXISTS ap_invoice_line_allocations (
  allocation_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_line_id      BIGINT NOT NULL,
  job_id               INT NULL COMMENT 'FK to jobs.id',
  cost_code_id         INT NULL COMMENT 'FK to ccpurchase.id',
  category             VARCHAR(64) NULL COMMENT 'Category from cats table',
  glcode               VARCHAR(64) NULL COMMENT 'GL account code',
  amount               DECIMAL(18,2) NOT NULL COMMENT 'Dollar amount allocated',
  percentage           DECIMAL(5,2) NULL COMMENT 'Percentage of line (optional)',
  created_by           VARCHAR(64) NOT NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_line_allocation (invoice_line_id, job_id, cost_code_id),
  KEY idx_invoice_line (invoice_line_id),
  KEY idx_job (job_id),
  KEY idx_cost_code (cost_code_id),
  CONSTRAINT fk_ail_invoice_line FOREIGN KEY (invoice_line_id) REFERENCES ap_invoice_lines(invoice_line_id) ON DELETE CASCADE
);

-- Approval routing for non-PO invoices
CREATE TABLE IF NOT EXISTS ap_approvals (
  approval_id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_id           BIGINT NOT NULL,
  approval_level       INT NOT NULL COMMENT '1=PM, 2=AM, 3=VP, 4=President',
  approval_role        VARCHAR(32) NOT NULL COMMENT 'PM, AM, VP, Pres',
  required_amount      DECIMAL(18,2) NOT NULL COMMENT 'Dollar threshold for this level',
  assigned_to          VARCHAR(256) NOT NULL COMMENT 'Email address of approver',
  status               ENUM('Pending','Approved','Rejected','Skipped') NOT NULL DEFAULT 'Pending',
  approved_by          VARCHAR(256) NULL,
  approved_at          DATETIME NULL,
  rejection_reason     TEXT NULL COMMENT 'Reason if rejected',
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_invoice (invoice_id),
  KEY idx_assigned (assigned_to),
  KEY idx_status (status),
  CONSTRAINT fk_app_invoice FOREIGN KEY (invoice_id) REFERENCES ap_invoices(invoice_id) ON DELETE CASCADE
);

-- Approval threshold configuration (admin-configurable)
CREATE TABLE IF NOT EXISTS ap_approval_thresholds (
  threshold_id         INT PRIMARY KEY AUTO_INCREMENT,
  approval_level       INT NOT NULL UNIQUE COMMENT '1=PM, 2=AM, 3=VP, 4=President',
  approval_role        VARCHAR(32) NOT NULL COMMENT 'PM, AM, VP, Pres',
  min_amount           DECIMAL(18,2) NOT NULL COMMENT 'Minimum amount for this level',
  max_amount           DECIMAL(18,2) NOT NULL COMMENT 'Maximum amount for this level',
  description          VARCHAR(255) NULL,
  active               TINYINT(1) NOT NULL DEFAULT 1,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default approval thresholds
INSERT INTO ap_approval_thresholds (approval_level, approval_role, min_amount, max_amount, description) VALUES
  (1, 'PM',      0.00,     5000.00,  'PM approval: $0-$5,000'),
  (2, 'AM',   5001.00,    50000.00,  'AM approval: $5,001-$50,000'),
  (3, 'VP',  50001.00,   200000.00,  'VP approval: $50,001-$200,000'),
  (4, 'Pres', 200001.00, 999999999.99, 'President approval: >$200,000');

-- Audit trail for approvals (tracks who approved/rejected and when)
CREATE TABLE IF NOT EXISTS ap_approval_audit (
  audit_id             BIGINT PRIMARY KEY AUTO_INCREMENT,
  approval_id          BIGINT NOT NULL,
  action               VARCHAR(32) NOT NULL COMMENT 'Approved, Rejected, Reassigned, Skipped',
  actor                VARCHAR(256) NOT NULL COMMENT 'Email of person taking action',
  notes                TEXT NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_approval (approval_id),
  KEY idx_actor (actor),
  CONSTRAINT fk_aaa_approval FOREIGN KEY (approval_id) REFERENCES ap_approvals(approval_id) ON DELETE CASCADE
);

-- Update status enum to include non-PO statuses
-- ALTER TABLE ap_invoices MODIFY COLUMN status ENUM('Draft','Matching','NeedsReview','Approved','ReadyForExport','Exported','Voided','PendingApproval','ApprovedByAM','ApprovedByVP') NOT NULL DEFAULT 'Draft';
