lti-api/internal/database/migrations/20251117034538_create_expense_realizations_table.up.sql

CREATE TABLE expense_realizations (
    id BIGSERIAL PRIMARY KEY,
    expense_nonstock_id BIGINT UNIQUE,
    realization_qty NUMERIC(15, 3) NOT NULL,
    realization_unit_price NUMERIC(15, 3) NOT NULL,
    realization_total_price NUMERIC(15, 3) NOT NULL,
    realization_date DATE NOT NULL,
    note TEXT,
    created_by BIGINT
);

-- Tambahkan Foreign Key ke expense_nonstocks
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'expense_nonstocks') THEN
        ALTER TABLE expense_realizations
            ADD CONSTRAINT fk_expense_realizations_nonstock_id 
            FOREIGN KEY (expense_nonstock_id) REFERENCES expense_nonstocks(id);
    END IF;
END $$;

-- Tambahkan Foreign Key ke users (created_by)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
        ALTER TABLE expense_realizations
            ADD CONSTRAINT fk_expense_realizations_created_by
            FOREIGN KEY (created_by) REFERENCES users(id);
    END IF;
END $$;

-- Index
CREATE INDEX idx_expense_realizations_nonstock_id ON expense_realizations (expense_nonstock_id);

CREATE INDEX idx_expense_realizations_date ON expense_realizations (realization_date);