Enrichissement et mapping - PT2QE¶

-- Derniers trimestres complets
SELECT
    ID_EXF || '_Q' || NO_TRF as QUARTER_CODE,
    MIN(ID_SEM) as START_DATE,
    MAX(ID_SEM) as END_DATE
FROM SYS_MD_CALENDRIER_SYSCO
WHERE MAX(ID_SEM) < SYSDATE
GROUP BY ID_EXF, NO_TRF
ORDER BY ID_EXF DESC, NO_TRF DESC
FETCH FIRST 4 ROWS ONLY;

WITH LAST_FG_HM AS (
    SELECT 
        ID_CLN_KEY,
        ID_CLN,
        ID_ART,
        FG_HM as LAST_FG_HM,
        ID_MERC_HM
    FROM (
        SELECT 
            f.ID_CLN_KEY,
            c.ID_CLN,
            f.ID_ART,

            -- Standardisation FG_HM
            CASE 
                WHEN TRIM(f.FG_HM) = 'X' THEN '1'
                ELSE '0'
            END AS FG_HM,

            TRIM(f.ID_MERC_HM) AS ID_MERC_HM,

            -- Sélection dernière transaction
            ROW_NUMBER() OVER (
                PARTITION BY c.ID_CLN, f.ID_ART 
                ORDER BY f.DT_CDE DESC, f.ID_FAC DESC
            ) as RN

        FROM SYS_FACTURE_LIGNE f
        JOIN SYS_MD_CLIENT c ON f.ID_CLN_KEY = c.ID_CLN_KEY
        JOIN SYS_MD_ARTICLE a ON f.ID_ART = a.ID_ART
        LEFT JOIN PRP_LABELS p ON f.ID_PRP = p.ID_PRP_KEY

        WHERE f.DT_CDE BETWEEN {start_date} AND {end_date}
            AND UPPER(TRIM(c.ID_TC_CG)) <> 'AUTRES'
            AND a.ID_GMM <> 'PSN'
            AND a.LC_HIC_SYSCO_N2 NOT IN ('Divers', 'Freezer')
            AND COALESCE(TRIM(f.FG_PRESTA), '0') = '0'
            AND COALESCE(TRIM(f.FG_MARCHANDISE), ' ') IN ('X', '1')
            AND f.MT_GM4 IS NOT NULL
            AND f.MT_CAB > 0
            AND f.QT_UF > 0
            AND p.ID_PRP_KEY IS NOT NULL  -- Exclut prix promo
    )
    WHERE RN = 1  -- Garde uniquement la dernière transaction
)

-- Dernière valeur FG_HM connue
SELECT 
    c.ID_CLN,
    c.LC_CLN,
    f.ID_ART,
    a.LC_ART,
    CASE WHEN TRIM(f.FG_HM) = 'X' THEN '1' ELSE '0' END as FG_HM,
    f.DT_CDE,
    f.ID_FAC
FROM SYS_FACTURE_LIGNE f
JOIN SYS_MD_CLIENT c ON f.ID_CLN_KEY = c.ID_CLN_KEY
JOIN SYS_MD_ARTICLE a ON f.ID_ART = a.ID_ART
WHERE c.ID_CLN = 'CLIENT123'
    AND f.ID_ART = 'ART456'
    AND f.DT_CDE >= ADD_MONTHS(SYSDATE, -12)
ORDER BY f.DT_CDE DESC, f.ID_FAC DESC
FETCH FIRST 1 ROW ONLY;

CASE
    -- ZOOM1 : RCI PI avec ou sans KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'PINDEP' THEN 'ZOOM1'

    -- ZOOM1 : RCI GI SANS KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GINDEP'
        AND ID_KAM = 'NO_KAM' THEN 'ZOOM1'

    -- ZOOM2 : RCI GI AVEC KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GINDEP'
        AND ID_KAM <> 'NO_KAM' THEN 'ZOOM2'

    -- ZOOM1 : FG_HM = '1' (hors mercuriale) - HORS RSC
    WHEN FG_HM = '1' AND (
        (ID_TC_CG = 'RSI' AND ID_TC_CIBLE IN ('MP_AO_', 'GR_A_G', 'MPMAPA', 'SRCREG', 'CA_REG', 'CR_REG'))
        OR (ID_TC_CG = 'RCI' AND ID_TC_CIBLE IN ('PERSYS', 'PERCLI', 'AS_AUT'))
    ) THEN 'ZOOM1'

    -- ZOOM1 : RSC avec ID_MERC_HM = 'HM'
    WHEN ID_TC_CG = 'RSC' AND ID_TC_CIBLE IN ('CR_NAT', 'SRCNAT', 'MP_NAT', 'CA_NAT')
        AND TRIM(ID_MERC_HM) = 'HM' THEN 'ZOOM1'

    -- ZOOM2 : FG_HM = '0' (mercuriale)
    WHEN FG_HM = '0' AND (
        (ID_TC_CG = 'RSI' AND ID_TC_CIBLE IN ('MP_AO_', 'SRCREG', 'MPMAPA', 'CA_REG', 'CR_REG'))
        OR (ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GC_REG')
    ) THEN 'ZOOM2'

    -- ZOOM2 : RCI GC_REG avec FG_HM = '1'
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GC_REG'
        AND FG_HM = '1' THEN 'ZOOM2'

    -- ZOOM3 : RSC avec ID_MERC_HM <> 'HM'
    WHEN ID_TC_CG = 'RSC' AND ID_TC_CIBLE IN ('SRCNAT', 'CR_NAT', 'MP_NAT', 'CA_NAT')
        AND TRIM(ID_MERC_HM) <> 'HM' THEN 'ZOOM3'

    -- ZOOM3 : RCC
    WHEN ID_TC_CG = 'RCC' AND ID_TC_CIBLE IN ('RCHNAT', 'GCN3PL', 'GCNDIR') THEN 'ZOOM3'

    ELSE 'HORS PERIMETRE'
END as UNIVERS

SELECT 
    c.ID_CLN,
    c.LC_CLN,
    c.ID_TC_CG,
    c.ID_TC_CIBLE,
    fh.FG_HM,
    c.ID_KAM,
    fh.ID_MERC_HM,

    -- Calcul UNIVERS
    CASE
        WHEN c.ID_TC_CG = 'RCI' AND c.ID_TC_CIBLE = 'PINDEP' THEN 'ZOOM1'
        -- [autres règles...]
        ELSE 'HORS PERIMETRE'
    END as UNIVERS_CALCULE

FROM SYS_MD_CLIENT c
LEFT JOIN LAST_FG_HM fh ON c.ID_CLN = fh.ID_CLN
WHERE c.ID_CLN = 'CLIENT123';

-- Ligne à ajouter dans le CASE
WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'NOUVEAU_TYPE' THEN 'ZOOM1'

-- Liste complète
SELECT 
    TYPE_CLIENT,
    UNIVERS,
    ID_TC_CG,
    ID_TC_CIBLE,
    FG_HM,
    PRB
FROM PT0CE_TYPE_CLIENT_MAPPING
ORDER BY UNIVERS, TYPE_CLIENT, FG_HM;

-- Compter par UNIVERS
SELECT 
    UNIVERS,
    COUNT(*) as NB_MAPPINGS,
    COUNT(DISTINCT TYPE_CLIENT) as NB_TYPE_CLIENT
FROM PT0CE_TYPE_CLIENT_MAPPING
GROUP BY UNIVERS
ORDER BY UNIVERS;

SELECT 
    ou.*,
    COALESCE(tc.TYPE_CLIENT, 'Hors référentiel') AS TYPE_CLIENT,
    -- [autres colonnes...]
FROM OFFERS_WITH_UNIVERS ou
LEFT JOIN PT0CE_TYPE_CLIENT_MAPPING tc
    ON tc.UNIVERS = ou.UNIVERS
    AND tc.ID_TC_CG = ou.ID_TC_CG
    AND tc.ID_TC_CIBLE = ou.ID_TC_CIBLE
    AND tc.FG_HM = ou.FG_HM
WHERE ou.UNIVERS = 'ZOOM1'  -- Filtre ZOOM1 immédiat
    AND tc.TYPE_CLIENT IS NOT NULL  -- Exclut offres non mappées

-- Test de jointure pour un client
SELECT 
    c.ID_CLN,
    c.LC_CLN,
    c.ID_TC_CG,
    c.ID_TC_CIBLE,
    fh.FG_HM,

    -- UNIVERS calculé (simpllifié ici)
    CASE 
        WHEN c.ID_TC_CG = 'RCI' AND c.ID_TC_CIBLE = 'PINDEP' THEN 'ZOOM1'
        ELSE 'AUTRE'
    END as UNIVERS,

    -- Résultat mapping
    tc.TYPE_CLIENT,
    tc.PRB

FROM SYS_MD_CLIENT c
LEFT JOIN LAST_FG_HM fh ON c.ID_CLN = fh.ID_CLN AND fh.ID_ART = 'ART123'
LEFT JOIN PT0CE_TYPE_CLIENT_MAPPING tc
    ON tc.UNIVERS = CASE WHEN c.ID_TC_CG = 'RCI' AND c.ID_TC_CIBLE = 'PINDEP' THEN 'ZOOM1' ELSE 'AUTRE' END
    AND tc.ID_TC_CG = c.ID_TC_CG
    AND tc.ID_TC_CIBLE = c.ID_TC_CIBLE
    AND tc.FG_HM = fh.FG_HM
WHERE c.ID_CLN = 'CLIENT123';

INSERT INTO PT0CE_TYPE_CLIENT_MAPPING (
    TYPE_CLIENT,
    UNIVERS,
    ID_TC_CG,
    ID_TC_CIBLE,
    FG_HM,
    PRB
) VALUES (
    'NOUVEAU TYPE CLIENT',
    'ZOOM1',
    'RCI',
    'NOUVEAU',
    '0',  -- Mercuriale
    1     -- PRB_RC
);

COMMIT;

UPDATE PT0CE_TYPE_CLIENT_MAPPING
SET TYPE_CLIENT = 'TYPE CLIENT MODIFIÉ'
WHERE UNIVERS = 'ZOOM1'
    AND ID_TC_CG = 'RCI'
    AND ID_TC_CIBLE = 'PINDEP'
    AND FG_HM = '0';

COMMIT;

DELETE FROM PT0CE_TYPE_CLIENT_MAPPING
WHERE UNIVERS = 'ZOOM1'
    AND ID_TC_CG = 'RCI'
    AND ID_TC_CIBLE = 'OBSOLETE';

COMMIT;

-- Avant exclusion
SELECT 
    c.ID_TC_CG,
    c.ID_TC_CIBLE,
    fh.FG_HM,
    COUNT(*) as NB_OFFRES
FROM SYS_MD_CONDITION cnd
JOIN SYS_MD_CLIENT c ON cnd.ID_CLN_KEY = c.ID_CLN_KEY
LEFT JOIN LAST_FG_HM fh ON c.ID_CLN = fh.ID_CLN
LEFT JOIN PT0CE_TYPE_CLIENT_MAPPING tc
    ON tc.UNIVERS = 'ZOOM1'  -- Exemple ZOOM1
    AND tc.ID_TC_CG = c.ID_TC_CG
    AND tc.ID_TC_CIBLE = c.ID_TC_CIBLE
    AND tc.FG_HM = fh.FG_HM
WHERE tc.TYPE_CLIENT IS NULL
    AND cnd.FG_CND_VLD = 'X'
GROUP BY c.ID_TC_CG, c.ID_TC_CIBLE, fh.FG_HM
ORDER BY NB_OFFRES DESC;

-- Liste complète
SELECT 
    LC_SFC_CIBLE,
    Type_Restaurant
FROM PT0CE_TYPE_RESTAURANT_MAPPING
ORDER BY Type_Restaurant;

-- Statistiques
SELECT 
    COUNT(*) as NB_TOTAL,
    COUNT(DISTINCT Type_Restaurant) as NB_TYPE_UNIQUES
FROM PT0CE_TYPE_RESTAURANT_MAPPING;

SELECT 
    ou.*,
    COALESCE(tr.Type_Restaurant, 'Hors référentiel') AS TYPE_RESTAURANT
FROM OFFERS_WITH_UNIVERS ou
LEFT JOIN PT0CE_TYPE_RESTAURANT_MAPPING tr
    ON tr.LC_SFC_CIBLE = ou.LC_SFC_CIBLE
WHERE ou.UNIVERS = 'ZOOM1'
    AND tr.Type_Restaurant IS NOT NULL;  -- Exclut offres non mappées

-- Test pour un client
SELECT 
    c.ID_CLN,
    c.LC_CLN,
    c.LC_SFC_CIBLE,
    tr.Type_Restaurant
FROM SYS_MD_CLIENT c
LEFT JOIN PT0CE_TYPE_RESTAURANT_MAPPING tr
    ON tr.LC_SFC_CIBLE = c.LC_SFC_CIBLE
WHERE c.ID_CLN = 'CLIENT123';

INSERT INTO PT0CE_TYPE_RESTAURANT_MAPPING (
    LC_SFC_CIBLE,
    Type_Restaurant
) VALUES (
    'NOUVEAU_CODE',
    'Nouveau Type Restaurant'
);

COMMIT;

UPDATE PT0CE_TYPE_RESTAURANT_MAPPING
SET Type_Restaurant = 'Type Restaurant Modifié'
WHERE LC_SFC_CIBLE = 'REST_TRAD';

COMMIT;

DELETE FROM PT0CE_TYPE_RESTAURANT_MAPPING
WHERE LC_SFC_CIBLE = 'CODE_OBSOLETE';

COMMIT;

SELECT 
    c.LC_SFC_CIBLE,
    COUNT(DISTINCT c.ID_CLN) as NB_CLIENTS
FROM SYS_MD_CLIENT c
LEFT JOIN PT0CE_TYPE_RESTAURANT_MAPPING tr
    ON tr.LC_SFC_CIBLE = c.LC_SFC_CIBLE
WHERE c.LC_SFC_CIBLE IS NOT NULL
    AND tr.Type_Restaurant IS NULL
GROUP BY c.LC_SFC_CIBLE
ORDER BY NB_CLIENTS DESC;

SELECT 
    c.ID_CLN,
    c.LC_ZDV_GRV as GEO
FROM SYS_MD_CLIENT c;

-- Distribution des GEO
SELECT 
    LC_ZDV_GRV as GEO,
    COUNT(DISTINCT ID_CLN) as NB_CLIENTS
FROM SYS_MD_CLIENT
WHERE LC_ZDV_GRV IS NOT NULL
GROUP BY LC_ZDV_GRV
ORDER BY NB_CLIENTS DESC;

SELECT 
    ID_CLN,
    LC_CLN,
    LC_ZDV_GRV as GEO
FROM SYS_MD_CLIENT
WHERE ID_CLN = 'CLIENT123';

-- Via PT2QE Menu - Option 5
-- Ou manuellement :
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'PT1CE_OPTIMAL_%'
ORDER BY table_name;

WITH CORRIDORS_ZOOM1_ONLY AS (
    -- Filtre ZOOM1 explicite (PT2QE actuel)
    SELECT * FROM PT1CE_OPTIMAL_ZOOM1
    WHERE STATUS = 'OPTIMAL'
),

-- TENTATIVE 1 : MASTER
MATCH_MASTER AS (
    SELECT 
        o.*,
        c.*,
        'MASTER' as MATCH_TYPE
    FROM PT2QE_PRICE_OFFERS o
    LEFT JOIN CORRIDORS_ZOOM1_ONLY c
        ON o.ID_ART = c.ID_ART
        AND o.TYPE_CLIENT = c.TYPE_CLIENT
        AND o.TYPE_RESTAURANT = c.TYPE_RESTAURANT
        AND NVL(o.GEO, 'NULL') = NVL(c.GEO, 'NULL')
        AND c.CUBE_TYPE = 'MASTER'
    WHERE c.ID_ART IS NOT NULL  -- Garde uniquement matchs trouvés
),

-- TENTATIVE 2 : NATIONAL (fallback)
MATCH_NATIONAL AS (
    SELECT 
        o.*,
        c.*,
        'NATIONAL' as MATCH_TYPE
    FROM PT2QE_PRICE_OFFERS o
    JOIN CORRIDORS_ZOOM1_ONLY c
        ON o.ID_ART = c.ID_ART
        AND c.TYPE_CLIENT = 'NATIONAL'
        AND c.TYPE_RESTAURANT = 'NATIONAL'
        AND (c.GEO = 'NATIONAL' OR c.GEO IS NULL)
        AND c.CUBE_TYPE = 'NATIONAL'
    WHERE NOT EXISTS (
        -- Uniquement si pas matché avec MASTER
        SELECT 1 FROM MATCH_MASTER m 
        WHERE m.ID_CLN = o.ID_CLN AND m.ID_ART = o.ID_ART
    )
),

-- Offres sans corridor
NO_MATCH AS (
    SELECT 
        o.*,
        CAST(NULL AS VARCHAR2(10)) as SOURCE_ZOOM,
        -- [autres colonnes NULL...]
        0 as HAS_CORRIDOR,
        'NO_MATCH' as MATCH_TYPE
    FROM PT2QE_PRICE_OFFERS o
    WHERE NOT EXISTS (SELECT 1 FROM MATCH_MASTER m WHERE m.ID_CLN = o.ID_CLN AND m.ID_ART = o.ID_ART)
        AND NOT EXISTS (SELECT 1 FROM MATCH_NATIONAL n WHERE n.ID_CLN = o.ID_CLN AND n.ID_ART = o.ID_ART)
)

-- Union finale
SELECT * FROM MATCH_MASTER
UNION ALL
SELECT * FROM MATCH_NATIONAL
UNION ALL
SELECT * FROM NO_MATCH;

-- Voir le type de match pour un client×article
SELECT 
    ID_CLN,
    LC_CLN,
    ID_ART,
    LC_ART,
    TYPE_CLIENT,
    TYPE_RESTAURANT,
    GEO,
    MATCH_TYPE,
    HAS_CORRIDOR,
    SOURCE_ZOOM
FROM PT2QE_PRICE_OFFERS_ENRICHED
WHERE ID_CLN = 'CLIENT123'
    AND ID_ART = 'ART456';

SELECT
    MATCH_TYPE,
    COUNT(*) as NB_OFFRES,
    COUNT(DISTINCT ID_CLN) as NB_CLIENTS,
    COUNT(DISTINCT ID_ART) as NB_ARTICLES
FROM PT2QE_PRICE_OFFERS_ENRICHED
GROUP BY MATCH_TYPE
ORDER BY MATCH_TYPE;

-- Corridor MASTER
SELECT 
    ID_ART,
    TYPE_CLIENT,
    TYPE_RESTAURANT,
    GEO,
    PAS_ACTIF,
    NEW_PAS,
    PRB_ACTIF,
    NEW_PRB,
    PRICE_SENSITIVITY
FROM PT1CE_OPTIMAL_ZOOM1
WHERE ID_ART = 'ART456'
    AND TYPE_CLIENT = 'RCI PI GI'
    AND CUBE_TYPE = 'MASTER';

-- Clients sans historique transactionnel
SELECT 
    c.ID_CLN,
    c.LC_CLN,
    c.ID_TC_CG,
    c.ID_TC_CIBLE,
    cnd.NB_CONDITIONS
FROM SYS_MD_CLIENT c
JOIN (
    SELECT ID_CLN_KEY, COUNT(*) as NB_CONDITIONS
    FROM SYS_MD_CONDITION
    WHERE FG_CND_VLD = 'X'
    GROUP BY ID_CLN_KEY
) cnd ON c.ID_CLN_KEY = cnd.ID_CLN_KEY
LEFT JOIN LAST_FG_HM fh ON c.ID_CLN = fh.ID_CLN
WHERE fh.LAST_FG_HM IS NULL
ORDER BY cnd.NB_CONDITIONS DESC;

-- Clients avec historique ancien
SELECT 
    c.ID_CLN,
    c.LC_CLN,
    fh.FG_HM,
    fh.LAST_TRANSACTION_DATE,
    TRUNC(MONTHS_BETWEEN(SYSDATE, fh.LAST_TRANSACTION_DATE)) as MOIS_INACTIVITE
FROM SYS_MD_CLIENT c
JOIN (
    SELECT 
        ID_CLN,
        FG_HM,
        MAX(DT_CDE) as LAST_TRANSACTION_DATE
    FROM LAST_FG_HM
    GROUP BY ID_CLN, FG_HM
) fh ON c.ID_CLN = fh.ID_CLN
WHERE MONTHS_BETWEEN(SYSDATE, fh.LAST_TRANSACTION_DATE) > 12
ORDER BY MOIS_INACTIVITE DESC;

-- Articles avec fallback NATIONAL
SELECT 
    ID_ART,
    LC_ART,
    COUNT(*) as NB_OFFRES_NATIONAL
FROM PT2QE_PRICE_OFFERS_ENRICHED
WHERE MATCH_TYPE = 'NATIONAL'
GROUP BY ID_ART, LC_ART
ORDER BY NB_OFFRES_NATIONAL DESC;

-- Offres avec enrichissement incomplet
SELECT 
    'TYPE_CLIENT' as DIMENSION_MANQUANTE,
    COUNT(*) as NB_OFFRES
FROM PT2QE_PRICE_OFFERS
WHERE TYPE_CLIENT IS NULL OR TYPE_CLIENT = 'Hors référentiel'

UNION ALL

SELECT 
    'TYPE_RESTAURANT' as DIMENSION_MANQUANTE,
    COUNT(*) as NB_OFFRES
FROM PT2QE_PRICE_OFFERS
WHERE TYPE_RESTAURANT IS NULL OR TYPE_RESTAURANT = 'Hors référentiel'

UNION ALL

SELECT 
    'GEO' as DIMENSION_MANQUANTE,
    COUNT(*) as NB_OFFRES
FROM PT2QE_PRICE_OFFERS
WHERE GEO IS NULL
    AND UNIVERS IN ('ZOOM1', 'ZOOM2');

SELECT COUNT(*) 
FROM PT1CE_OPTIMAL_ZOOM1
WHERE STATUS = 'OPTIMAL';
-- Doit retourner > 0

-- Types clients SAP présents mais non mappés
SELECT DISTINCT
    c.ID_TC_CG,
    c.ID_TC_CIBLE,
    COUNT(DISTINCT c.ID_CLN) as NB_CLIENTS
FROM SYS_MD_CLIENT c
LEFT JOIN PT0CE_TYPE_CLIENT_MAPPING tc
    ON tc.ID_TC_CG = c.ID_TC_CG
    AND tc.ID_TC_CIBLE = c.ID_TC_CIBLE
WHERE tc.TYPE_CLIENT IS NULL
    AND c.ID_TC_CG <> 'AUTRES'
GROUP BY c.ID_TC_CG, c.ID_TC_CIBLE
ORDER BY NB_CLIENTS DESC;

-- Pour chaque combinaison trouvée
INSERT INTO PT0CE_TYPE_CLIENT_MAPPING (
    TYPE_CLIENT, UNIVERS, ID_TC_CG, ID_TC_CIBLE, FG_HM, PRB
) VALUES (
    'NOUVEAU TYPE',  -- À déterminer
    'ZOOM1',         -- Selon règles UNIVERS
    'RCI',           -- ID_TC_CG trouvé
    'NOUVEAU',       -- ID_TC_CIBLE trouvé
    '0',             -- '0' pour mercuriale
    1                -- 1=PRB_RC, 2=PRB_COLL
);

-- Ajouter aussi la variante FG_HM='1' si nécessaire
INSERT INTO PT0CE_TYPE_CLIENT_MAPPING (
    TYPE_CLIENT, UNIVERS, ID_TC_CG, ID_TC_CIBLE, FG_HM, PRB
) VALUES (
    'NOUVEAU TYPE HM',
    'ZOOM1',
    'RCI',
    'NOUVEAU',
    '1',  -- Hors mercuriale
    1
);

COMMIT;

SELECT DISTINCT
    c.LC_SFC_CIBLE,
    COUNT(DISTINCT c.ID_CLN) as NB_CLIENTS
FROM SYS_MD_CLIENT c
LEFT JOIN PT0CE_TYPE_RESTAURANT_MAPPING tr
    ON tr.LC_SFC_CIBLE = c.LC_SFC_CIBLE
WHERE c.LC_SFC_CIBLE IS NOT NULL
    AND tr.Type_Restaurant IS NULL
GROUP BY c.LC_SFC_CIBLE
ORDER BY NB_CLIENTS DESC;

INSERT INTO PT0CE_TYPE_RESTAURANT_MAPPING (
    LC_SFC_CIBLE,
    Type_Restaurant
) VALUES (
    'NOUVEAU_SFC',
    'Nouveau Type Restaurant'
);

COMMIT;

CREATE INDEX IDX_PT0CE_TC_MAP 
ON PT0CE_TYPE_CLIENT_MAPPING (ID_TC_CG, ID_TC_CIBLE, UNIVERS, FG_HM)
COMPRESS;

CREATE INDEX IDX_PT0CE_TR_MAP 
ON PT0CE_TYPE_RESTAURANT_MAPPING (LC_SFC_CIBLE)
COMPRESS;

-- Index composite pour matching corridors
CREATE INDEX IDX_PT2QE_ENRICHED_DIMS 
ON PT2QE_PRICE_OFFERS_ENRICHED (ID_ART, TYPE_CLIENT, TYPE_RESTAURANT, GEO)
COMPRESS;

-- Index sur client
CREATE INDEX IDX_PT2QE_ENRICHED_CLN 
ON PT2QE_PRICE_OFFERS_ENRICHED (ID_CLN)
COMPRESS;

-- Index sur une table
SELECT 
    index_name,
    column_name,
    column_position
FROM user_ind_columns
WHERE table_name = 'PT0CE_TYPE_CLIENT_MAPPING'
ORDER BY index_name, column_position;

-- Checklist SQL complète
SELECT 'PT1CE ZOOM1' as CHECK_NAME, COUNT(*) as VALUE FROM PT1CE_OPTIMAL_ZOOM1 WHERE STATUS = 'OPTIMAL'
UNION ALL
SELECT 'MAPPING TYPE_CLIENT', COUNT(*) FROM PT0CE_TYPE_CLIENT_MAPPING
UNION ALL
SELECT 'MAPPING TYPE_RESTAURANT', COUNT(*) FROM PT0CE_TYPE_RESTAURANT_MAPPING
UNION ALL
SELECT 'CALENDRIER FISCAL', COUNT(DISTINCT ID_EXF || '_Q' || NO_TRF) FROM SYS_MD_CALENDRIER_SYSCO;

-- ATTENTION : Supprime toutes les données PT2QE
BEGIN
    FOR t IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'PT2QE_%') LOOP
        EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' PURGE';
    END LOOP;
END;
/

-- Différences de TYPE_CLIENT entre 2 runs
SELECT 
    r1.ID_CLN,
    r1.ID_ART,
    r1.TYPE_CLIENT as TYPE_CLIENT_RUN1,
    r2.TYPE_CLIENT as TYPE_CLIENT_RUN2
FROM PT2QE_PRICE_OFFERS_RUN1 r1
JOIN PT2QE_PRICE_OFFERS_RUN2 r2
    ON r1.ID_CLN = r2.ID_CLN
    AND r1.ID_ART = r2.ID_ART
WHERE r1.TYPE_CLIENT <> r2.TYPE_CLIENT;