lti-api/internal/database/migrations/20251127070744_create_project_budgets.up.sql

CREATE TABLE project_budgets (
    id BIGSERIAL PRIMARY KEY,
    project_flock_id BIGINT NOT NULL,
    nonstock_id BIGINT NOT NULL,
    qty NUMERIC(15, 3) NOT NULL,
    price NUMERIC(15, 3) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Tambahkan Foreign Key ke project_flocks
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flocks') THEN
        ALTER TABLE project_budgets
            ADD CONSTRAINT fk_project_budgets_project_flock_id 
            FOREIGN KEY (project_flock_id) REFERENCES project_flocks(id);
    END IF;
END $$;
-- Tambahkan Foreign Key ke nonstocks
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'nonstocks') THEN
        ALTER TABLE project_budgets
            ADD CONSTRAINT fk_project_budgets_nonstock_id 
            FOREIGN KEY (nonstock_id) REFERENCES nonstocks(id);
    END IF;
END $$;
-- Index
CREATE INDEX idx_project_budgets_project_flock_id ON project_budgets (project_flock_id);

CREATE INDEX idx_project_budgets_nonstock_id ON project_budgets (nonstock_id);