BEGIN;

ALTER TABLE org.departments
    ADD COLUMN IF NOT EXISTS cost_center_id bigint NULL,
    ADD COLUMN IF NOT EXISTS location_id bigint NULL,
    ADD COLUMN IF NOT EXISTS annual_budget numeric(18, 2) NULL,
    ADD COLUMN IF NOT EXISTS currency_code character varying(10) NULL DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS description text NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'departments_cost_center_id_fkey'
          AND conrelid = 'org.departments'::regclass
    ) THEN
        ALTER TABLE org.departments
            ADD CONSTRAINT departments_cost_center_id_fkey
            FOREIGN KEY (cost_center_id) REFERENCES org.cost_centers(id);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'departments_location_id_fkey'
          AND conrelid = 'org.departments'::regclass
    ) THEN
        ALTER TABLE org.departments
            ADD CONSTRAINT departments_location_id_fkey
            FOREIGN KEY (location_id) REFERENCES org.locations(id);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_org_departments_cost_center_id
    ON org.departments (cost_center_id)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_org_departments_location_id
    ON org.departments (location_id)
    WHERE is_deleted = FALSE;

ALTER TABLE org.designations
    ADD COLUMN IF NOT EXISTS reports_to_designation_id bigint NULL,
    ADD COLUMN IF NOT EXISTS salary_band character varying(100) NULL,
    ADD COLUMN IF NOT EXISTS salary_min numeric(18, 2) NULL,
    ADD COLUMN IF NOT EXISTS salary_max numeric(18, 2) NULL,
    ADD COLUMN IF NOT EXISTS planned_headcount integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS open_positions integer NOT NULL DEFAULT 0;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'designations_reports_to_designation_id_fkey'
          AND conrelid = 'org.designations'::regclass
    ) THEN
        ALTER TABLE org.designations
            ADD CONSTRAINT designations_reports_to_designation_id_fkey
            FOREIGN KEY (reports_to_designation_id) REFERENCES org.designations(id);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_org_designations_reports_to_designation_id
    ON org.designations (reports_to_designation_id)
    WHERE is_deleted = FALSE;

COMMIT;