Merchant Limit

create table team_kingkong.mid_limits_shivam as (
WITH latest_limits AS (
    SELECT
        merchantid,
        identifier,
        maxamtpermonth,
        ROW_NUMBER() OVER (PARTITION BY merchantid, identifier ORDER BY modifieddate DESC) AS rn
    FROM (
        SELECT
            merchantid,
            identifier,
            maxamtpermonth,
            (DATE_DIFF('millisecond', TIMESTAMP '1970-01-01 5:30:00', CAST(modifieddate AS TIMESTAMP))) AS modifieddate
        FROM merchant_velocity.instrument_historic_data_snapshot_v3
        WHERE dl_last_updated >= DATE '2010-01-01'
        
        UNION ALL
        
        SELECT
            merchantid,
            identifier,
            maxamtpermonth,
            modifieddate
        FROM TP_S_2022_MD_EVENTLOG_001.TP_S_2022_MD_EVENTLOG_001_snapshot_v3
        WHERE dl_last_updated >= DATE '2010-01-01'
    )
),

pivoted_limits AS (
    SELECT
        merchantid,
        MAX(CASE WHEN identifier = 'UPI_CC' THEN maxamtpermonth END) AS UPI_CC_limit,
        MAX(CASE WHEN identifier = 'UPI' THEN maxamtpermonth END) AS UPI_limit,
        MAX(CASE WHEN identifier = 'CC' THEN maxamtpermonth END) AS CC_limit,
        MAX(CASE WHEN identifier = 'DC' THEN maxamtpermonth END) AS DC_limit,
        MAX(CASE WHEN identifier = 'UPI_CREDITLINE' THEN maxamtpermonth END) AS UPI_CREDITLINE_limit,
        MAX(CASE WHEN identifier = 'PER_MID' THEN maxamtpermonth END) AS overall_limit
    FROM latest_limits
    WHERE rn = 1
    GROUP BY merchantid
),

merchant_types AS (
    SELECT 
        v1.merchantid,
        CASE 
            WHEN o_mid IS NOT NULL THEN 'Online'
            WHEN e_mid IS NOT NULL THEN 'EDC' 
            ELSE 'QR' 
        END AS EDC_QR
    FROM pivoted_limits v1
    LEFT JOIN (
        SELECT DISTINCT merchant_id AS o_mid
        FROM datalake.online_payment_merchants
    ) m_3 ON v1.merchantid = m_3.o_mid
    LEFT JOIN (
        SELECT DISTINCT mid AS e_mid
        FROM paytmpgdb.entity_edc_info_snapshot_v3
        WHERE terminal_status = 'ACTIVE'
        AND dl_last_updated >= DATE '2010-01-01'
    ) m_4 ON v1.merchantid = m_4.e_mid
)
select * from (
SELECT
    p.merchantid,
    m.EDC_QR,
    CAST(p.UPI_CC_limit AS double)/100 AS UPI_CC_limit,
    CAST(p.UPI_limit AS double)/100 AS UPI_limit,
    CAST(p.CC_limit AS double)/100 AS CC_limit,
    CAST(p.DC_limit AS double)/100 AS DC_limit,
    CAST(p.UPI_CREDITLINE_limit AS double)/100 AS UPI_CREDITLINE_limit,
    CAST(p.overall_limit AS double)/100 AS overall_limit
    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) 
    --     THEN '1' ELSE '0' 
    -- END AS unlimited_overall_limit_flag,
    
    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) OR (p.UPI_CC_limit IS NULL OR p.UPI_CC_limit = -1) THEN 'NA'
    --     WHEN p.UPI_CC_limit > p.overall_limit THEN '1' 
    --     ELSE '0' 
    -- END AS UPI_CC_limit_ov_lmt_flag,

    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) OR (p.UPI_limit IS NULL OR p.UPI_limit = -1) THEN 'NA'
    --     WHEN p.UPI_limit > p.overall_limit THEN '1' 
    --     ELSE '0' 
    -- END AS UPI_limit_ov_lmt_flag,

    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) OR (p.CC_limit IS NULL OR p.CC_limit = -1) THEN 'NA'
    --     WHEN p.CC_limit > p.overall_limit THEN '1' 
    --     ELSE '0' 
    -- END AS CC_limit_ov_lmt_flag,

    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) OR (p.DC_limit IS NULL OR p.DC_limit = -1) THEN 'NA'
    --     WHEN p.DC_limit > p.overall_limit THEN '1' 
    --     ELSE '0' 
    -- END AS DC_limit_ov_lmt_flag,

    -- CASE 
    --     WHEN (p.overall_limit IS NULL OR p.overall_limit = -1) OR (p.UPI_CREDITLINE_limit IS NULL OR p.UPI_CREDITLINE_limit = -1) THEN 'NA'
    --     WHEN p.UPI_CREDITLINE_limit > p.overall_limit THEN '1' 
    --     ELSE '0' 
    -- END AS UPI_CREDITLINE_limit_ov_lmt_flag,

    -- CASE 
    --     WHEN (p.UPI_limit IS NULL OR p.UPI_limit = -1) OR (p.UPI_CC_limit IS NULL OR p.UPI_CC_limit = -1) THEN 'NA'
    --     WHEN p.UPI_CC_limit > p.UPI_limit THEN '1' 
    --     ELSE '0' 
    -- END AS UPI_CC_limit_UPI_limit_flag,

    -- CASE 
    --     WHEN (p.UPI_limit IS NULL OR p.UPI_limit = -1) OR (p.UPI_CREDITLINE_limit IS NULL OR p.UPI_CREDITLINE_limit = -1) THEN 'NA'
    --     WHEN p.UPI_CREDITLINE_limit > p.UPI_limit THEN '1' 
    --     ELSE '0' 
    -- END AS UPI_CREDITLINE_limit_UPI_limit_flag
FROM pivoted_limits p
JOIN merchant_types m ON p.merchantid = m.merchantid));
-- where UPI_CC_limit_ov_lmt_flag = '1'  OR UPI_limit_ov_lmt_flag = '1'
--     OR CC_limit_ov_lmt_flag = '1' OR DC_limit_ov_lmt_flag = '1'
--     OR UPI_CREDITLINE_limit_ov_lmt_flag = '1' OR UPI_CC_limit_UPI_limit_flag = '1'