BEGIN;

CREATE SCHEMA IF NOT EXISTS inventory;
CREATE SCHEMA IF NOT EXISTS purchases;
CREATE SCHEMA IF NOT EXISTS sales;
CREATE SCHEMA IF NOT EXISTS suppliers;
CREATE SCHEMA IF NOT EXISTS warehouse;
CREATE SCHEMA IF NOT EXISTS pos;
CREATE SCHEMA IF NOT EXISTS manufacturing;

ALTER TABLE IF EXISTS inventory.items
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS purchase_uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS sales_uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS purchase_unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS sales_unit_of_measure varchar(40);

ALTER TABLE IF EXISTS suppliers.supplier_products
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;

ALTER TABLE IF EXISTS purchases.purchase_request_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS purchases.purchase_order_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS purchases.goods_receipt_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS purchases.purchase_return_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS purchases.purchase_discrepancy_cases ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS purchases.inventory_release_events ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;

ALTER TABLE IF EXISTS sales.quotation_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS sales.sales_order_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS sales.sales_delivery_note_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS sales.sales_return_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS sales.sales_credit_note_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;

ALTER TABLE IF EXISTS inventory.stock_balances
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.stock_movements
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.stock_reservations
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.stock_count_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.stock_adjustment_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.stock_transfer_items ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.scrap_requests
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.inventory_variance_cases
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS inventory.reorder_rules
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;

ALTER TABLE IF EXISTS warehouse.receiving_task_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS warehouse.putaway_task_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS warehouse.pick_list_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS warehouse.dispatch_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS warehouse.transfer_order_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;
ALTER TABLE IF EXISTS warehouse.bin_movements
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40),
    ADD COLUMN IF NOT EXISTS uom_id bigint REFERENCES settings.unit_of_measures(id) ON DELETE SET NULL;

CREATE OR REPLACE FUNCTION settings.sync_uom_columns(p_table text)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_table regclass;
    v_schema text;
    v_name text;
    v_has_tenant boolean;
    v_has_code boolean;
    v_has_id boolean;
    v_id_accepts_settings boolean;
BEGIN
    v_table := to_regclass(p_table);
    IF v_table IS NULL THEN
        RETURN;
    END IF;

    SELECT n.nspname, c.relname
      INTO v_schema, v_name
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE c.oid = v_table;

    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
         WHERE table_schema = v_schema AND table_name = v_name AND column_name = 'tenant_id'
    ) INTO v_has_tenant;
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
         WHERE table_schema = v_schema AND table_name = v_name AND column_name = 'unit_of_measure'
    ) INTO v_has_code;
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
         WHERE table_schema = v_schema AND table_name = v_name AND column_name = 'uom_id'
    ) INTO v_has_id;

    IF NOT v_has_tenant OR NOT v_has_code THEN
        RETURN;
    END IF;

    EXECUTE format(
        'UPDATE %s target
            SET unit_of_measure = upper(COALESCE(NULLIF(target.unit_of_measure, ''''), ''EA''))
          WHERE target.tenant_id IS NOT NULL
            AND (target.unit_of_measure IS NULL OR target.unit_of_measure = '''')',
        v_table
    );

    IF NOT v_has_id THEN
        RETURN;
    END IF;

    SELECT NOT EXISTS (
        SELECT 1
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON kcu.constraint_schema = tc.constraint_schema
           AND kcu.constraint_name = tc.constraint_name
           AND kcu.table_schema = tc.table_schema
           AND kcu.table_name = tc.table_name
          JOIN information_schema.constraint_column_usage ccu
            ON ccu.constraint_schema = tc.constraint_schema
           AND ccu.constraint_name = tc.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY'
           AND tc.table_schema = v_schema
           AND tc.table_name = v_name
           AND kcu.column_name = 'uom_id'
    )
    OR EXISTS (
        SELECT 1
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON kcu.constraint_schema = tc.constraint_schema
           AND kcu.constraint_name = tc.constraint_name
           AND kcu.table_schema = tc.table_schema
           AND kcu.table_name = tc.table_name
          JOIN information_schema.constraint_column_usage ccu
            ON ccu.constraint_schema = tc.constraint_schema
           AND ccu.constraint_name = tc.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY'
           AND tc.table_schema = v_schema
           AND tc.table_name = v_name
           AND kcu.column_name = 'uom_id'
           AND ccu.table_schema = 'settings'
           AND ccu.table_name = 'unit_of_measures'
    ) INTO v_id_accepts_settings;

    IF NOT v_id_accepts_settings THEN
        RETURN;
    END IF;

    EXECUTE format(
        'UPDATE %s target
            SET uom_id = uom.id
         FROM settings.unit_of_measures uom
         WHERE target.tenant_id = uom.tenant_id
           AND lower(uom.uom_code) = lower(COALESCE(NULLIF(target.unit_of_measure, ''''), ''EA''))
           AND uom.is_active = true
           AND uom.deleted_at IS NULL
           AND (target.uom_id IS NULL OR target.uom_id <> uom.id)',
        v_table
    );
END;
$$;

SELECT settings.sync_uom_columns('inventory.items');
UPDATE inventory.items
SET purchase_unit_of_measure = COALESCE(NULLIF(purchase_unit_of_measure, ''), unit_of_measure, 'EA'),
    sales_unit_of_measure = COALESCE(NULLIF(sales_unit_of_measure, ''), unit_of_measure, 'EA')
WHERE COALESCE(purchase_unit_of_measure, sales_unit_of_measure, unit_of_measure) IS NULL
   OR purchase_unit_of_measure IS NULL
   OR sales_unit_of_measure IS NULL;

DO $$
DECLARE
    v_purchase_accepts_settings boolean;
    v_sales_accepts_settings boolean;
BEGIN
    SELECT EXISTS (
        SELECT 1
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON kcu.constraint_schema = tc.constraint_schema
           AND kcu.constraint_name = tc.constraint_name
           AND kcu.table_schema = tc.table_schema
           AND kcu.table_name = tc.table_name
          JOIN information_schema.constraint_column_usage ccu
            ON ccu.constraint_schema = tc.constraint_schema
           AND ccu.constraint_name = tc.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY'
           AND tc.table_schema = 'inventory'
           AND tc.table_name = 'items'
           AND kcu.column_name = 'purchase_uom_id'
           AND ccu.table_schema = 'settings'
           AND ccu.table_name = 'unit_of_measures'
    ) INTO v_purchase_accepts_settings;

    SELECT EXISTS (
        SELECT 1
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON kcu.constraint_schema = tc.constraint_schema
           AND kcu.constraint_name = tc.constraint_name
           AND kcu.table_schema = tc.table_schema
           AND kcu.table_name = tc.table_name
          JOIN information_schema.constraint_column_usage ccu
            ON ccu.constraint_schema = tc.constraint_schema
           AND ccu.constraint_name = tc.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY'
           AND tc.table_schema = 'inventory'
           AND tc.table_name = 'items'
           AND kcu.column_name = 'sales_uom_id'
           AND ccu.table_schema = 'settings'
           AND ccu.table_name = 'unit_of_measures'
    ) INTO v_sales_accepts_settings;

    IF v_purchase_accepts_settings THEN
        UPDATE inventory.items item
        SET purchase_uom_id = purchase_uom.id
        FROM settings.unit_of_measures purchase_uom
        WHERE purchase_uom.tenant_id = item.tenant_id
          AND lower(purchase_uom.uom_code) = lower(COALESCE(NULLIF(item.purchase_unit_of_measure, ''), item.unit_of_measure, 'EA'))
          AND purchase_uom.is_active = true
          AND purchase_uom.deleted_at IS NULL
          AND item.purchase_uom_id IS NULL;
    END IF;

    IF v_sales_accepts_settings THEN
        UPDATE inventory.items item
        SET sales_uom_id = sales_uom.id
        FROM settings.unit_of_measures sales_uom
        WHERE sales_uom.tenant_id = item.tenant_id
          AND lower(sales_uom.uom_code) = lower(COALESCE(NULLIF(item.sales_unit_of_measure, ''), item.unit_of_measure, 'EA'))
          AND sales_uom.is_active = true
          AND sales_uom.deleted_at IS NULL
          AND item.sales_uom_id IS NULL;
    END IF;
END $$;

SELECT settings.sync_uom_columns('suppliers.supplier_products');
SELECT settings.sync_uom_columns('purchases.purchase_request_items');
SELECT settings.sync_uom_columns('purchases.purchase_order_items');
SELECT settings.sync_uom_columns('purchases.goods_receipt_items');
SELECT settings.sync_uom_columns('purchases.purchase_return_items');
SELECT settings.sync_uom_columns('purchases.purchase_discrepancy_cases');
SELECT settings.sync_uom_columns('purchases.inventory_release_events');
SELECT settings.sync_uom_columns('sales.quotation_items');
SELECT settings.sync_uom_columns('sales.sales_order_items');
SELECT settings.sync_uom_columns('sales.sales_delivery_note_items');
SELECT settings.sync_uom_columns('sales.sales_return_items');
SELECT settings.sync_uom_columns('sales.sales_credit_note_items');
SELECT settings.sync_uom_columns('inventory.stock_balances');
SELECT settings.sync_uom_columns('inventory.stock_movements');
SELECT settings.sync_uom_columns('inventory.stock_reservations');
SELECT settings.sync_uom_columns('inventory.stock_count_items');
SELECT settings.sync_uom_columns('inventory.stock_adjustment_items');
SELECT settings.sync_uom_columns('inventory.stock_transfer_items');
SELECT settings.sync_uom_columns('inventory.scrap_requests');
SELECT settings.sync_uom_columns('inventory.inventory_variance_cases');
SELECT settings.sync_uom_columns('inventory.reorder_rules');
SELECT settings.sync_uom_columns('warehouse.receiving_task_items');
SELECT settings.sync_uom_columns('warehouse.putaway_task_items');
SELECT settings.sync_uom_columns('warehouse.pick_list_items');
SELECT settings.sync_uom_columns('warehouse.dispatch_items');
SELECT settings.sync_uom_columns('warehouse.transfer_order_items');
SELECT settings.sync_uom_columns('warehouse.bin_movements');

DROP FUNCTION settings.sync_uom_columns(text);

DO $$
BEGIN
    IF to_regclass('inventory.items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_inventory_items_uom ON inventory.items (tenant_id, uom_id);
    END IF;
    IF to_regclass('inventory.stock_balances') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_inventory_stock_balances_uom ON inventory.stock_balances (tenant_id, uom_id);
    END IF;
    IF to_regclass('inventory.stock_movements') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_inventory_stock_movements_uom ON inventory.stock_movements (tenant_id, uom_id);
    END IF;
    IF to_regclass('inventory.stock_transfer_items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_inventory_transfer_items_uom ON inventory.stock_transfer_items (tenant_id, uom_id);
    END IF;
    IF to_regclass('purchases.purchase_order_items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_purchase_order_items_uom ON purchases.purchase_order_items (tenant_id, uom_id);
    END IF;
    IF to_regclass('purchases.goods_receipt_items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_goods_receipt_items_uom ON purchases.goods_receipt_items (tenant_id, uom_id);
    END IF;
    IF to_regclass('sales.sales_order_items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_sales_order_items_uom ON sales.sales_order_items (tenant_id, uom_id);
    END IF;
    IF to_regclass('sales.sales_delivery_note_items') IS NOT NULL THEN
        CREATE INDEX IF NOT EXISTS idx_sales_delivery_note_items_uom ON sales.sales_delivery_note_items (tenant_id, uom_id);
    END IF;
END $$;

COMMIT;