-- Create finance tables and migrate existing case data

-- Lookup tables
CREATE TABLE IF NOT EXISTS invoice_types (
    id SERIAL PRIMARY KEY,
    name varchar(100) NOT NULL,
    description text DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS invoice_types_tenant_idx ON invoice_types(tenant_id);

CREATE TABLE IF NOT EXISTS expense_types (
    id SERIAL PRIMARY KEY,
    name varchar(100) NOT NULL,
    description text DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS expense_types_tenant_idx ON expense_types(tenant_id);

CREATE TABLE IF NOT EXISTS payment_types (
    id SERIAL PRIMARY KEY,
    name varchar(100) NOT NULL,
    description text DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS payment_types_tenant_idx ON payment_types(tenant_id);

-- Main tables
CREATE TABLE IF NOT EXISTS invoices (
    id SERIAL PRIMARY KEY,
    type_id INTEGER NOT NULL,
    reference_table varchar(100) DEFAULT NULL,
    reference_id INTEGER DEFAULT NULL,
    invoice_number varchar(100) NOT NULL,
    invoice_date DATE DEFAULT NULL,
    amount DECIMAL(10,2) DEFAULT 0,
    status varchar(50) DEFAULT NULL,
    sent_at TIMESTAMP DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS invoices_type_idx ON invoices(type_id);
CREATE INDEX IF NOT EXISTS invoices_ref_idx ON invoices(reference_table, reference_id);
CREATE INDEX IF NOT EXISTS invoices_tenant_idx ON invoices(tenant_id);

CREATE TABLE IF NOT EXISTS expenses (
    id SERIAL PRIMARY KEY,
    type_id INTEGER NOT NULL,
    reference_table varchar(100) DEFAULT NULL,
    reference_id INTEGER DEFAULT NULL,
    expense_date DATE DEFAULT NULL,
    amount DECIMAL(10,2) DEFAULT 0,
    description text DEFAULT NULL,
    employee_id INTEGER DEFAULT NULL,
    document_id INTEGER DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS expenses_type_idx ON expenses(type_id);
CREATE INDEX IF NOT EXISTS expenses_ref_idx ON expenses(reference_table, reference_id);
CREATE INDEX IF NOT EXISTS expenses_employee_idx ON expenses(employee_id);
CREATE INDEX IF NOT EXISTS expenses_document_idx ON expenses(document_id);
CREATE INDEX IF NOT EXISTS expenses_tenant_idx ON expenses(tenant_id);

CREATE TABLE IF NOT EXISTS payments (
    id SERIAL PRIMARY KEY,
    type_id INTEGER NOT NULL,
    invoice_id INTEGER NOT NULL,
    payment_date DATE DEFAULT NULL,
    amount DECIMAL(10,2) DEFAULT 0,
    payment_method varchar(50) DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS payments_type_idx ON payments(type_id);
CREATE INDEX IF NOT EXISTS payments_invoice_idx ON payments(invoice_id);
CREATE INDEX IF NOT EXISTS payments_tenant_idx ON payments(tenant_id);

-- Seed default type values
INSERT INTO invoice_types (name) VALUES ('case') ON CONFLICT DO NOTHING;
INSERT INTO expense_types (name) VALUES ('case') ON CONFLICT DO NOTHING;
INSERT INTO payment_types (name) VALUES ('case') ON CONFLICT DO NOTHING;

-- Migrate existing case invoice records
INSERT INTO invoices (id, type_id, reference_table, reference_id, invoice_number, invoice_date,
                      amount, status, sent_at, tenant_id, isDeleted, updatedAt)
SELECT ci.id, (SELECT id FROM invoice_types WHERE name='case' LIMIT 1), 'cases', ci.case_id,
       ci.invoice_number, ci.invoice_date, ci.amount, ci.status, ci.sent_at,
       ci.tenant_id, ci.isDeleted, ci.updatedAt
FROM case_invoices ci;
SELECT setval(pg_get_serial_sequence('invoices','id'), (SELECT COALESCE(MAX(id),0) FROM invoices)+1, false);

-- Migrate existing case expense records
INSERT INTO expenses (id, type_id, reference_table, reference_id, expense_date, amount,
                      description, employee_id, document_id, tenant_id, isDeleted, updatedAt)
SELECT ce.id, (SELECT id FROM expense_types WHERE name='case' LIMIT 1), 'cases', ce.case_id,
       ce.expense_date, ce.amount, ce.description, ce.employee_id, ce.document_id,
       ce.tenant_id, ce.isDeleted, ce.updatedAt
FROM case_expenses ce;
SELECT setval(pg_get_serial_sequence('expenses','id'), (SELECT COALESCE(MAX(id),0) FROM expenses)+1, false);

-- Migrate existing case payment records
INSERT INTO payments (id, type_id, invoice_id, payment_date, amount, payment_method,
                      tenant_id, isDeleted, updatedAt)
SELECT cp.id, (SELECT id FROM payment_types WHERE name='case' LIMIT 1),
       cp.case_invoice_id, cp.payment_date, cp.amount, cp.payment_method,
       cp.tenant_id, cp.isDeleted, cp.updatedAt
FROM case_payments cp;
SELECT setval(pg_get_serial_sequence('payments','id'), (SELECT COALESCE(MAX(id),0) FROM payments)+1, false);

