-- Create generic_invoices table mirroring quotations structure
-- This table is separate from the case management invoices table

CREATE TABLE IF NOT EXISTS generic_invoices (
    invoice_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    invoice_number TEXT,
    date DATE NOT NULL DEFAULT CURRENT_DATE,
    customer_id UUID NOT NULL,
    org_id UUID,
    client_id UUID,
    line_items JSONB DEFAULT '[]'::jsonb,
    note TEXT,
    terms TEXT,
    status TEXT DEFAULT 'draft', -- draft, sent, paid, cancelled, overdue
    total_amount NUMERIC(10,2) DEFAULT 0,
    tax_amount NUMERIC(10,2) DEFAULT 0,
    amount_paid NUMERIC(10,2) DEFAULT 0,
    due_date DATE,
    
    -- Link to original quotation if converted
    quotation_id UUID,
    
    -- TaxCore integration
    taxcore_submission_id VARCHAR(100),
    taxcore_submission_id_fk UUID,
    taxcore_receipt_data JSONB,
    taxcore_submitted_at TIMESTAMPTZ,
    taxcore_verification_url TEXT,
    taxcore_qr_code TEXT,
    
    -- Audit fields
    created_by UUID,
    updated_by UUID,
    isdeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedat TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Foreign keys
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    FOREIGN KEY (org_id) REFERENCES orgs(org_id) ON DELETE CASCADE,
    FOREIGN KEY (quotation_id) REFERENCES quotations(quotation_id) ON DELETE SET NULL
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_generic_invoices_customer ON generic_invoices(customer_id);
CREATE INDEX IF NOT EXISTS idx_generic_invoices_org ON generic_invoices(org_id);
CREATE INDEX IF NOT EXISTS idx_generic_invoices_status ON generic_invoices(status);
CREATE INDEX IF NOT EXISTS idx_generic_invoices_date ON generic_invoices(date);
CREATE INDEX IF NOT EXISTS idx_generic_invoices_quotation ON generic_invoices(quotation_id);
CREATE INDEX IF NOT EXISTS idx_generic_invoices_updatedat ON generic_invoices(updatedat);

-- Add to tenant_tables for sync
INSERT INTO tenant_tables (name, sync_direction, conflict_resolution, last_modified)
VALUES ('generic_invoices', 'bidirectional', 'server_wins', NOW())
ON CONFLICT (name) DO NOTHING;

COMMENT ON TABLE generic_invoices IS 'Generic invoices table separate from case management invoices. Mirrors quotations structure.';
COMMENT ON COLUMN generic_invoices.quotation_id IS 'Reference to original quotation if this invoice was converted from a quote';
COMMENT ON COLUMN generic_invoices.status IS 'Invoice status: draft, sent, paid, cancelled, overdue';
