namespace AccountsDAL.Query.Warehouse
{
    // One-time/repeatable ETL for DIMOU — per the GB5 Data Warehouse developer guide section 09.2,
    // DIMOU has 0 rows on a fresh environment (e.g. TRANSDEV/GB5DEMO before this endpoint exists),
    // so every BI query joining DIMOU returns NULL for Company/Branch/Division/City/Region/Country
    // names until this runs. This is an admin action that replaces the whole dimension on each run
    // (MERGE upsert), not a full CRUD interface — DIMOU has no natural "create one row" UI use case.
    //
    // Source chain verified live against TRANSDEV (2026-08-26) via INFORMATION_SCHEMA.COLUMNS and
    // sys.foreign_keys — MORGANIZATIONUNIT/MCOMPANY/MBRANCH/MDIVISION have NO City/Region/Country
    // columns at all (the PDF's own MERGE example elides this with "..."), so those three values are
    // resolved via MORGANIZATIONUNIT.DEFAULTADDRESSID -> MADDRESS -> MCITY/MREGION(via
    // STANDARDREGIONID)/MCOUNTRY, the same LEFT JOIN MADDRESS ON AD.ADDRESSID = x.DEFAULTADDRESSID
    // pattern already used in PartyDetailQB.cs. DIMOU's columns are all NOT NULL, so every LEFT JOIN
    // side is wrapped in ISNULL(...,-1) for ID columns and ISNULL(...,'NONE') for code/name columns —
    // matching the -1/'NONE' sentinel convention DIMPARTY's own DDL already established
    // (Analytics_Phase1A_Warehouse_Schema_SqlServer.sql: DF_DIMPARTY_LOCATIONID DEFAULT -1,
    // DF_DIMPARTY_LOCATIONCODE DEFAULT 'NONE').
    public static class WarehouseDimQB
    {
        public const string POPULATE_DIMOU = @"
MERGE DIMOU AS target
USING (
    SELECT
        OU.OUID,
        OU.ORGANIZATIONUNITCODE                    AS OUCODE,
        OU.ORGANIZATIONUNITNAME                    AS OUNAME,
        ISNULL(C.COMPANYID, -1)                    AS COMPANYID,
        ISNULL(C.COMPANYCODE, 'NONE')              AS COMPANYCODE,
        ISNULL(C.COMPANYNAME, 'NONE')              AS COMPANYNAME,
        ISNULL(B.BRANCHID, -1)                     AS BRANCHID,
        ISNULL(B.BRANCHCODE, 'NONE')               AS BRANCHCODE,
        ISNULL(B.BRANCHNAME, 'NONE')               AS BRANCHNAME,
        ISNULL(D.DIVISIONID, -1)                   AS DIVISIONID,
        ISNULL(D.DIVISIONCODE, 'NONE')             AS DIVISIONCODE,
        ISNULL(D.DIVISIONNAME, 'NONE')             AS DIVISIONNAME,
        ISNULL(CI.CITYID, -1)                      AS CITYID,
        ISNULL(CI.CITYCODE, 'NONE')                AS CITYCODE,
        ISNULL(CI.CITYNAME, 'NONE')                AS CITYNAME,
        ISNULL(RG.REGIONID, -1)                    AS REGIONID,
        ISNULL(RG.REGIONCODE, 'NONE')              AS REGIONCODE,
        ISNULL(RG.REGIONNAME, 'NONE')              AS REGIONNAME,
        ISNULL(CO.COUNTRYID, -1)                   AS COUNTRYID,
        ISNULL(CO.COUNTRYCODE, 'NONE')             AS COUNTRYCODE,
        ISNULL(CO.COUNTRYNAME, 'NONE')             AS COUNTRYNAME
    FROM MORGANIZATIONUNIT OU
    LEFT JOIN MCOMPANY  C  ON C.COMPANYID  = OU.COMPANYID
    LEFT JOIN MBRANCH   B  ON B.BRANCHID   = OU.BRANCHID
    LEFT JOIN MDIVISION D  ON D.DIVISIONID = OU.DIVISIONID
    LEFT JOIN MADDRESS  AD ON AD.ADDRESSID = OU.DEFAULTADDRESSID
    LEFT JOIN MCITY     CI ON CI.CITYID    = AD.CITYID
    LEFT JOIN MREGION   RG ON RG.REGIONID  = AD.STANDARDREGIONID
    LEFT JOIN MCOUNTRY  CO ON CO.COUNTRYID = AD.COUNTRYID
    WHERE OU.TENANTID = @TenantId
      AND OU.STATUS <> 2
) AS source
ON target.OUID = source.OUID
WHEN MATCHED THEN UPDATE SET
    OUCODE       = source.OUCODE,
    OUNAME       = source.OUNAME,
    COMPANYID    = source.COMPANYID,
    COMPANYCODE  = source.COMPANYCODE,
    COMPANYNAME  = source.COMPANYNAME,
    BRANCHID     = source.BRANCHID,
    BRANCHCODE   = source.BRANCHCODE,
    BRANCHNAME   = source.BRANCHNAME,
    DIVISIONID   = source.DIVISIONID,
    DIVISIONCODE = source.DIVISIONCODE,
    DIVISIONNAME = source.DIVISIONNAME,
    CITYID       = source.CITYID,
    CITYCODE     = source.CITYCODE,
    CITYNAME     = source.CITYNAME,
    REGIONID     = source.REGIONID,
    REGIONCODE   = source.REGIONCODE,
    REGIONNAME   = source.REGIONNAME,
    COUNTRYID    = source.COUNTRYID,
    COUNTRYCODE  = source.COUNTRYCODE,
    COUNTRYNAME  = source.COUNTRYNAME,
    MODIFIEDON   = GETUTCDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (OUID, OUCODE, OUNAME, COMPANYID, COMPANYCODE, COMPANYNAME,
            BRANCHID, BRANCHCODE, BRANCHNAME, DIVISIONID, DIVISIONCODE, DIVISIONNAME,
            CITYID, CITYCODE, CITYNAME, REGIONID, REGIONCODE, REGIONNAME,
            COUNTRYID, COUNTRYCODE, COUNTRYNAME, MODIFIEDON)
    VALUES (source.OUID, source.OUCODE, source.OUNAME, source.COMPANYID, source.COMPANYCODE, source.COMPANYNAME,
            source.BRANCHID, source.BRANCHCODE, source.BRANCHNAME, source.DIVISIONID, source.DIVISIONCODE, source.DIVISIONNAME,
            source.CITYID, source.CITYCODE, source.CITYNAME, source.REGIONID, source.REGIONCODE, source.REGIONNAME,
            source.COUNTRYID, source.COUNTRYCODE, source.COUNTRYNAME, GETUTCDATE())
OUTPUT $action;";

        // OUs currently posted in FSALE/FFINANCE but missing from DIMOU after a run — a sanity check
        // for the endpoint's response, not used by the MERGE itself.
        public const string COUNT_DIMOU_ROWS = @"SELECT COUNT(*) FROM DIMOU";
    }
}