lti-api/internal/database/migrations/20251029074825_create_laying_transfers_table.up.sql

CREATE TABLE IF NOT EXISTS laying_transfers (
    id BIGSERIAL PRIMARY KEY,
    transfer_number VARCHAR(50) UNIQUE NOT NULL,
    from_project_flock_id BIGINT NOT NULL,
    to_project_flock_id BIGINT NOT NULL,
    transfer_date DATE NOT NULL,
    pending_usage_qty NUMERIC(15, 3),
    usage_qty NUMERIC(15, 3),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    deleted_at TIMESTAMPTZ,
    created_by BIGINT NOT NULL
);

-- FOREIGN KEYS (dijalankan setelah semua tabel parent ada)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'project_flocks') THEN
        ALTER TABLE laying_transfers
            ADD CONSTRAINT fk_laying_from_project_flock
            FOREIGN KEY (from_project_flock_id)
            REFERENCES project_flocks(id)
            ON DELETE RESTRICT ON UPDATE CASCADE;
        ALTER TABLE laying_transfers
            ADD CONSTRAINT fk_laying_to_project_flock
            FOREIGN KEY (to_project_flock_id)
            REFERENCES project_flocks(id)
            ON DELETE RESTRICT ON UPDATE CASCADE;
    END IF;

    IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'users') THEN
        ALTER TABLE laying_transfers
            ADD CONSTRAINT fk_laying_created_by
            FOREIGN KEY (created_by)
            REFERENCES users(id)
            ON DELETE RESTRICT ON UPDATE CASCADE;
    END IF;
END $$;

-- INDEXES
CREATE UNIQUE INDEX IF NOT EXISTS idx_laying_transfers_transfer_number ON laying_transfers (transfer_number)
WHERE
    deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_laying_transfers_from_project_flock_id ON laying_transfers (from_project_flock_id);

CREATE INDEX IF NOT EXISTS idx_laying_transfers_to_project_flock_id ON laying_transfers (to_project_flock_id);

CREATE INDEX IF NOT EXISTS idx_laying_transfers_created_by ON laying_transfers (created_by);

CREATE INDEX IF NOT EXISTS idx_laying_transfers_deleted_at ON laying_transfers (deleted_at);