CREATE TABLE expense_types (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY expense_types_code_uq (code),
    INDEX idx_expense_types_active (is_active)
);

CREATE TABLE expense_categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    expense_type_id INT UNSIGNED NOT NULL,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(140) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY expense_categories_code_uq (code),
    INDEX idx_expense_categories_type (expense_type_id),
    INDEX idx_expense_categories_active (is_active),
    CONSTRAINT expense_categories_type_fk FOREIGN KEY (expense_type_id)
        REFERENCES expense_types (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

ALTER TABLE expenses
    ADD COLUMN expense_type_id INT UNSIGNED NULL AFTER description,
    ADD COLUMN expense_category_id INT UNSIGNED NULL AFTER expense_type_id,
    ADD COLUMN status ENUM('pending','paid','canceled') NOT NULL DEFAULT 'pending' AFTER expense_category_id,
    ADD COLUMN document_type_id INT UNSIGNED NULL AFTER status,
    ADD COLUMN document_number VARCHAR(60) NULL AFTER document_type_id,
    ADD COLUMN voucher_url TEXT NULL AFTER document_number,
    ADD COLUMN payer_name VARCHAR(120) NULL AFTER voucher_url,
    ADD COLUMN related_employee_id INT UNSIGNED NULL AFTER payer_name,
    ADD COLUMN related_project_id INT UNSIGNED NULL AFTER related_employee_id,
    ADD COLUMN sunat_period VARCHAR(7) NULL AFTER related_project_id,
    ADD COLUMN is_recurring TINYINT(1) NOT NULL DEFAULT 0 AFTER sunat_period,
    ADD COLUMN calendar_event_id INT UNSIGNED NULL AFTER is_recurring,
    ADD COLUMN created_by INT UNSIGNED NULL AFTER notes,
    ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD COLUMN deleted_at DATETIME NULL AFTER updated_at;

CREATE INDEX idx_expenses_status ON expenses (status);
CREATE INDEX idx_expenses_type ON expenses (expense_type_id);
CREATE INDEX idx_expenses_category ON expenses (expense_category_id);
CREATE INDEX idx_expenses_employee ON expenses (related_employee_id);
CREATE INDEX idx_expenses_project ON expenses (related_project_id);

ALTER TABLE expenses
    ADD CONSTRAINT expenses_type_fk FOREIGN KEY (expense_type_id)
        REFERENCES expense_types (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_category_fk FOREIGN KEY (expense_category_id)
        REFERENCES expense_categories (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_document_fk FOREIGN KEY (document_type_id)
        REFERENCES document_types (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_employee_fk FOREIGN KEY (related_employee_id)
        REFERENCES employees (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_project_fk FOREIGN KEY (related_project_id)
        REFERENCES projects (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_calendar_fk FOREIGN KEY (calendar_event_id)
        REFERENCES calendar_events (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    ADD CONSTRAINT expenses_created_by_fk FOREIGN KEY (created_by)
        REFERENCES users (id) ON DELETE SET NULL ON UPDATE RESTRICT;

INSERT INTO expense_types (code, name, is_active) VALUES
    ('fixed', 'Costos fijos', 1),
    ('variable', 'Costos variables', 1),
    ('operational', 'Costos operativos', 1),
    ('financial', 'Gastos financieros', 1),
    ('asset', 'Activos', 1);

INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'salaries', 'Sueldos', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'services', 'Servicios', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'accountant', 'Contadora', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'office', 'Local', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'internet', 'Internet', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'phone', 'Teléfono', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'subscriptions', 'Suscripciones', 1 FROM expense_types WHERE code = 'fixed';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'taxes', 'Impuestos', 1 FROM expense_types WHERE code = 'fixed';

INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'sales_commissions', 'Comisiones ventas', 1 FROM expense_types WHERE code = 'variable';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'outsourced_services', 'Servicios tercerizados', 1 FROM expense_types WHERE code = 'variable';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'afp', 'AFP', 1 FROM expense_types WHERE code = 'variable';

INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'travel', 'Viáticos', 1 FROM expense_types WHERE code = 'operational';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'stationery', 'Papelería', 1 FROM expense_types WHERE code = 'operational';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'training', 'Capacitación', 1 FROM expense_types WHERE code = 'operational';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'courses', 'Cursos', 1 FROM expense_types WHERE code = 'operational';

INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'interest', 'Intereses', 1 FROM expense_types WHERE code = 'financial';
INSERT INTO expense_categories (expense_type_id, code, name, is_active)
SELECT id, 'bank_fees', 'Comisiones bancarias', 1 FROM expense_types WHERE code = 'financial';