-- Zoho Invoice → MKL AR sync.
--
-- Mirrors invoices created in Zoho Invoice (the standalone product, not CRM)
-- into ar_invoices so MKL AR can become the system of record. Scope: invoices
-- with Zoho date >= 2026-01-01.
--
-- ar_invoices.zoho_invoice_id was previously used for the Zoho CRM Invoices
-- module record id (long-tail historical imports). The Zoho Invoice product
-- has its own invoice_id namespace, so we add a separate column rather than
-- overload the existing one.
--
-- Payments come from Zoho's /customerpayments endpoint. A single Zoho payment
-- can be split across many invoices (one customer check applied to 21 MTS
-- invoices is a real case), so we store one ar_payments row per allocation
-- and dedup with UNIQUE (zoho_payment_id, invoice_id).

ALTER TABLE ar_invoices
    ADD COLUMN IF NOT EXISTS zoho_invoice_app_id VARCHAR(50) DEFAULT NULL AFTER zoho_invoice_id,
    ADD COLUMN IF NOT EXISTS zoho_status VARCHAR(30) DEFAULT NULL AFTER status,
    ADD COLUMN IF NOT EXISTS zoho_last_modified DATETIME DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_synced_at DATETIME DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_last_payment_date DATE DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_payment_made DECIMAL(14,2) DEFAULT NULL;

-- Unique index for upsert key. Using a unique index (not constraint) so multiple
-- NULLs are allowed (placeholder rows from createInvoiceFromOrder before they
-- exist in Zoho).
CREATE UNIQUE INDEX IF NOT EXISTS uq_ar_zoho_app_id ON ar_invoices (zoho_invoice_app_id);
CREATE INDEX IF NOT EXISTS idx_ar_zoho_modified ON ar_invoices (zoho_last_modified);

ALTER TABLE ar_payments
    ADD COLUMN IF NOT EXISTS zoho_payment_id VARCHAR(50) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_payment_number VARCHAR(40) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_payment_mode VARCHAR(40) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS zoho_synced_at DATETIME DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS source ENUM('local','zoho_sync','chase_matcher') DEFAULT 'local';

-- Composite unique key so a single Zoho payment split across N invoices creates
-- N rows, but a re-sync of the same payment is a no-op.
CREATE UNIQUE INDEX IF NOT EXISTS uq_arp_zoho_payment ON ar_payments (zoho_payment_id, invoice_id);
CREATE INDEX IF NOT EXISTS idx_arp_zoho_payment_id ON ar_payments (zoho_payment_id);

-- Track last successful run timestamp for the cron's incremental cursor. Single
-- row keyed by job name.
CREATE TABLE IF NOT EXISTS ar_sync_state (
    job_name VARCHAR(50) PRIMARY KEY,
    last_run_at DATETIME DEFAULT NULL,
    last_success_at DATETIME DEFAULT NULL,
    cursor_value VARCHAR(100) DEFAULT NULL,
    last_result_json LONGTEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;