Schema

CREATE TABLE "FactBillingDetails" (
    "ActiveFlag" SMALLINT,
    "CashDiscount" DOUBLE PRECISION,
    "CGSTAmount" DOUBLE PRECISION,
    "CGSTPercentage" DOUBLE PRECISION,
    "CreatedBySource" VARCHAR(355),
    "CreatedDate" INTEGER,
    "CreationDateSource" INTEGER,
    "ClientCode" INTEGER,
    "ClientCodeOriginal" INTEGER,
    "UpdatedBySource" INTEGER,
    "UpdationDateSource" INTEGER,
    "Value" DOUBLE PRECISION,
    "VATAmount" DOUBLE PRECISION,
    "VATPercentage" DOUBLE PRECISION,
    "Volume" DOUBLE PRECISION,
    "VolumeInKg" DOUBLE PRECISION,
    "VolumeInLtr" DOUBLE PRECISION,
    "YearMonth" INTEGER,
    "DamageQuantity" DOUBLE PRECISION,
    "ShipToCode" VARCHAR(180),
    "DistributorCode" VARCHAR(180),
    "DistributorCodeDP" VARCHAR(180),
    "RegionKey" VARCHAR(150),
    "DeletedFlag" SMALLINT,
    "DiscountAmount" DOUBLE PRECISION,
    "DivisionCode" SMALLINT,
    "FOCQuantity" DOUBLE PRECISION,
    "FreightAmount" DOUBLE PRECISION,
    "IGSTAmount" DOUBLE PRECISION,
    "IGSTPercentage" DOUBLE PRECISION,
    "InvoiceDate" INTEGER,
    "InvoiceDateDF" VARCHAR(150),
    "InvoiceNo" VARCHAR(150),
    "InvoicePrintNumber" VARCHAR(160),
    "ItemCode" VARCHAR(200),
    "ItemDeterminationCode" VARCHAR(120),
    "ItemWeight" DOUBLE PRECISION,
    "LoadFlag" VARCHAR(150),
    "MfgListNo" VARCHAR(150),
    "MISType" VARCHAR(150),
    "MRP" DOUBLE PRECISION,
    "NetWeightInKg" DOUBLE PRECISION,
    "OrderNo" VARCHAR(200),
    "PrintDate" VARCHAR(150),
    "PrinterName" VARCHAR(200),
    "Rate" DOUBLE PRECISION,
    "ReceivableDate" INTEGER,
    "RecordType" VARCHAR(150),
    "ReportingUnit" DOUBLE PRECISION,
    "ReportingUnitInEach" DOUBLE PRECISION,
    "ReportingValue" DOUBLE PRECISION,
    "RevisedNetValueMSGVG" DOUBLE PRECISION,
    "RevisedNetValueMVG" DOUBLE PRECISION,
    "RouteCode" VARCHAR(150),
    "RouteName" VARCHAR(200),
    "TeamCode" SMALLINT,
    "SchemeAmount" DOUBLE PRECISION,
    "SGSTAmount" DOUBLE PRECISION,
    "SGSTPercentage" DOUBLE PRECISION,
    "StarDiscount" DOUBLE PRECISION,
    "StockRequiredQuantity" DOUBLE PRECISION,
    "TCSPer" DOUBLE PRECISION,
    "TCSVal" DOUBLE PRECISION,
    "TransferDate" INTEGER,
    "TSICode" VARCHAR(150),
    "TSIName" VARCHAR(200),
    "Type" VARCHAR(355)
);

-------

CREATE TABLE user_accessible_regions (
    user_id      TEXT              NOT NULL,
    region_key   VARCHAR           NOT NULL,
    PRIMARY KEY (user_id, region_key)
);

CREATE INDEX idx_user_regions_region_key ON user_accessible_regions(region_key);
CREATE INDEX idx_user_regions_user_id     ON user_accessible_regions(user_id);

-------

CREATE OR REPLACE FUNCTION refresh_user_regions(p_user_id TEXT) RETURNS VOID AS $$
BEGIN
    -- Delete existing entries for this user
    DELETE FROM user_accessible_regions WHERE user_id = p_user_id;

    -- Insert new accessible region entries for this user
    INSERT INTO user_accessible_regions (user_id, region_key)
    SELECT DISTINCT
        p_user_id,
        region."RegionKey"::character varying  -- Explicit cast to match types
    FROM
        region_master region
        JOIN client_master client ON region."ClientCode"::TEXT = client."ClientCode"::TEXT
        JOIN access_master access ON client."TerritoryCode" = access."TerritoryCode"
    WHERE
        region."ActiveFlag" = 'True' AND
        access."Path" ~ (
            (
                '*.'
                || LOWER(
                    REPLACE(
                    REPLACE(
                    REPLACE(
                    REPLACE(
                    REPLACE(
                        p_user_id
                    ,'@','_at_')
                    ,'.','_dot_')
                    ,'-','_')
                    ,' ','_')
                    ,'__','_')
                )
                || '.*'
            )::lquery
        );

    RETURN;
END;
$$ LANGUAGE plpgsql;