SELECT
    TO_CHAR(b_date, 'YYYY-MM')                                  AS month,
    SUM(bill_value)::BIGINT                                     AS ns,
    COUNT(DISTINCT CASE WHEN tran_type = 'SALE' THEN bill_no END) AS b
FROM tran.txn
WHERE b_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
  AND location_code = :store
GROUP BY 1
ORDER BY 1;

SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        sub_tran_type,
        'OMNI-ONLINE','Omni'),'AMZNOW','Amazon'),'INSTI','Insti'),
        'AMZB2B','Insti'),'OFFLIN','Offline'),'HOMEDE','HD'),
        'HOMECD','HD'),'OFFREG','Offline')                       AS channel,
    SUM(CASE WHEN tran_type = 'SALE' THEN bill_value ELSE 0 END)::BIGINT AS s,
    COUNT(DISTINCT CASE WHEN tran_type = 'SALE' THEN bill_no END) AS b
FROM tran.txn
WHERE b_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND location_code = :store
GROUP BY 1
ORDER BY 2 DESC;

SELECT
    i.division_name                                                            AS d,
    SUM(CASE WHEN b_date >= DATE_TRUNC('month', CURRENT_DATE)
        THEN bill_value END)::BIGINT                                           AS mtd,
    SUM(CASE WHEN b_date <  DATE_TRUNC('month', CURRENT_DATE)
        THEN bill_value END)::BIGINT                                           AS pm
FROM tran.txn t
    INNER JOIN master.item i ON t.item_no::varchar = i.item
WHERE t.b_date     >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND t.tran_type   = 'SALE'
  AND t.location_code = :store
GROUP BY 1
ORDER BY mtd DESC;

SELECT ml.loc AS store_code, ml.loc_name AS store_name,
    COUNT(*) AS phones_collected,
    SUM(CASE WHEN d.valid_flag = 'Valid' THEN 1 ELSE 0 END) AS phones_valid,
    MIN(d.insert_time)::date AS collection_started,
    MAX(d.insert_time)::date AS collection_latest
FROM stardust.d2d_contacts d
    INNER JOIN master.loc ml ON ml.loc_name = d.store_name
GROUP BY ml.loc, ml.loc_name;

WITH seg AS (
    SELECT segment_id, definition
    FROM qt.segment
    WHERE definition ILIKE '%ml.loc%'
),
analytics_agg AS (
    SELECT campaign_id,
        SUM(CASE WHEN metric = 'SENT' THEN value ELSE 0 END) AS sent,
        SUM(CASE WHEN metric = 'DELIVERED' THEN value ELSE 0 END) AS delivered,
        SUM(CASE WHEN metric = 'READ' THEN value ELSE 0 END) AS read_,
        SUM(CASE WHEN metric = 'CONVERTED' THEN value ELSE 0 END) AS converted
    FROM qt.analytics
    GROUP BY campaign_id
)
SELECT c.campaign_id, c.name AS campaign_name, c.segment_id, s.definition,
    c.created_at::date AS created_date,
    COALESCE(a.sent, 0) AS sent, COALESCE(a.delivered, 0) AS delivered,
    COALESCE(a.read_, 0) AS read_, COALESCE(a.converted, 0) AS converted
FROM qt.campaign c
    INNER JOIN seg s ON s.segment_id = c.segment_id
    LEFT JOIN analytics_agg a ON a.campaign_id = c.campaign_id
WHERE c.name ILIKE '%nso%';

SELECT s.location_code AS store_code, s.loc_name AS store_name, s.dt AS scorecard_date,
    s.availability_perc, s.fnv_availability_perc,
    s.hv_bad_inv_perc, s.fnv_bad_inv_perc,
    s.min_1_2_cogsperday_perc, s.congestion_perc,
    s.nps, s.service_nps, s.quality_nps, s.total_nps_responses,
    s.retail_growth_perc, s.total_soh, s.total_bad_inventory,
    s.nsv_l4w, s.void_mrp
FROM analytics.store_health_scorecard s
    INNER JOIN master.loc l ON l.loc = s.location_code
WHERE s.dt = (SELECT MAX(dt) FROM analytics.store_health_scorecard)
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132));

-- Anchoring on jedi_master ensures the full active roster shows up - absentees too.
-- Same pattern dash.more.in uses. employee_status='A' + date_of_exit IS NULL filters to active staff.
SELECT l.loc AS store_code, jm.full_name, jm.designation, jm.employee_type,
       jm.attendance_shift AS shift, jm.attendance_weeklyoff AS weekly_off,
       LEFT(a.clock_in::varchar, 5)  AS clock_in,
       LEFT(a.clock_out::varchar, 5) AS clock_out,
       CASE WHEN a.employee_id IS NULL THEN 1 ELSE 0 END AS absent,
       CASE WHEN jm.attendance_weeklyoff = TO_CHAR(CURRENT_DATE, 'FMDay') THEN 1 ELSE 0 END AS is_weekly_off
FROM hrms.jedi_master jm
    INNER JOIN master.loc l ON l.location_fin = jm.work_area_code_x
    LEFT JOIN hrms.attendance a ON a.employee_id = jm.employee_id AND a.date = CURRENT_DATE
WHERE jm.employee_status = 'A' AND jm.date_of_exit IS NULL
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132))
ORDER BY l.loc, absent ASC, is_weekly_off DESC, a.clock_in NULLS LAST, jm.full_name;

SELECT to_loc AS location_code,
    (received_date AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date AS dt,
    CASE WHEN distro_type = 'A' THEN 'Alloc'
         WHEN distro_type = 'T' THEN 'TO' ELSE 'Oth' END AS type,
    COUNT(DISTINCT distro_no) AS shipments,
    COUNT(DISTINCT item)      AS items,
    ROUND(SUM(received_qty))  AS qty,
    TO_CHAR(MIN(received_date AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata'), 'HH24:MI') AS first_grn,
    TO_CHAR(MAX(received_date AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata'), 'HH24:MI') AS last_grn
FROM tran.shipment
WHERE received_date >= CURRENT_DATE - 3
  AND received_qty > 0
  AND to_loc IN (SELECT loc FROM master.loc
                 WHERE open_date >= CURRENT_DATE - INTERVAL '365 days' OR loc IN (1600, 4132))
GROUP BY 1, 2, 3
ORDER BY 1, 2 DESC, 3;

-- Latest snapshot per store (matches dash.more.in behaviour).
SELECT nh.loc AS store_code, nh.module, nh.section,
    COUNT(*) AS total_tasks,
    SUM(CASE WHEN nh.is_completed = 1 THEN 1 ELSE 0 END) AS completed,
    ROUND(SUM(CASE WHEN nh.is_completed = 1 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(*), 0), 1) AS pct
FROM sim.newhope_dashboard nh
    INNER JOIN master.loc l ON l.loc = nh.loc
WHERE nh.create_date::date = (
        SELECT MAX(create_date::date) FROM sim.newhope_dashboard WHERE loc = nh.loc
      )
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132))
GROUP BY nh.loc, nh.module, nh.section;

-- Same task can repeat within a snapshot (e.g. OCP per shift). Group + count avoids duplicate rows.
SELECT nh.loc AS store_code, nh.module, nh.section, nh.task,
       COUNT(*) AS task_count,
       TO_CHAR(MAX(nh.modify_date), 'DD-Mon HH24:MI') AS last_updated
FROM sim.newhope_dashboard nh
    INNER JOIN master.loc l ON l.loc = nh.loc
WHERE nh.create_date::date = (
        SELECT MAX(create_date::date) FROM sim.newhope_dashboard WHERE loc = nh.loc
      )
  AND nh.is_completed = 0
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132))
GROUP BY nh.loc, nh.module, nh.section, nh.task
ORDER BY nh.loc, nh.module, nh.section, nh.task;

SELECT m.loc AS location_code,
    m.date::date AS dt,
    SUM(COALESCE(m.dump, 0))::BIGINT      AS dump,
    SUM(COALESCE(m.shrinkage, 0))::BIGINT AS shr,
    SUM(COALESCE(m.expiry, 0))::BIGINT    AS exp,
    SUM(COALESCE(m.net_sales, 0))::BIGINT AS ns
FROM analytics.margin m
    INNER JOIN master.loc l ON l.loc = m.loc
WHERE m.date >= CURRENT_DATE - 14
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132))
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT s.location_code, s.dt,
    s.void_mrp, s.billed_value_l4w,
    ROUND(s.void_mrp * 100.0 / NULLIF(s.void_mrp + s.billed_value_l4w, 0), 2) AS shrink_pct,
    s.fraud_tw_bills, s.fraud_tw_mt2_bills, s.fraud_tw_mt2_sales,
    s.fraud_lw_bills, s.fraud_lw_mt2_bills, s.fraud_lw_mt2_sales
FROM analytics.store_health_scorecard s
WHERE s.dt = (SELECT MAX(dt) FROM analytics.store_health_scorecard);

WITH rated AS (
    SELECT es.location_code, es.bill_date, es.session_id,
        MAX(CASE WHEN ec.enps_key = 'nps_score' THEN LOWER(ec.enps_value) END) AS rating
    FROM qt.enps_sessions es
        INNER JOIN qt.enps_conversations ec ON es.session_id = ec.session_id
    WHERE es.bill_date >= CURRENT_DATE - 30
      AND ec.enps_key = 'nps_score'
    GROUP BY 1, 2, 3
)
SELECT location_code, bill_date AS date, COUNT(*) AS total_responses,
    SUM(CASE WHEN rating = 'good' THEN 1 ELSE 0 END) AS promoters,
    SUM(CASE WHEN rating = 'bad'  THEN 1 ELSE 0 END) AS detractors,
    SUM(CASE WHEN rating = 'neutral' THEN 1 ELSE 0 END) AS passives,
    ROUND((SUM(CASE WHEN rating='good' THEN 1.0 ELSE 0 END)
         - SUM(CASE WHEN rating='bad'  THEN 1.0 ELSE 0 END))
         / NULLIF(COUNT(*), 0) * 100, 1) AS enps_score
FROM rated WHERE rating IS NOT NULL
GROUP BY 1, 2;

-- form_data stores the customer's open-text WhatsApp Flow response.
-- Shape: {"L2": {"question": "What can we improve?", "response": ""}}
-- enps_value at out_context='End' is the flow tag (goodflow_L2 / badflow_L2) - used for sentiment.
SELECT es.location_code AS store_code, es.bill_date AS date,
       ec.enps_value AS flow_tag, ec.form_data
FROM qt.enps_sessions es
    INNER JOIN qt.enps_conversations ec ON es.session_id = ec.session_id
WHERE es.bill_date >= CURRENT_DATE - 10
  AND ec.out_context = 'End'
  AND ec.form_data IS NOT NULL
  AND LEN(ec.form_data) > 5
ORDER BY es.bill_date DESC;

SELECT l.loc AS store_code, l.loc_name, l.open_date::date, l.format, l.cohort,
    l.bill_addr, l.bill_city, l.bill_state, l.bill_pincode,
    l.bill_contact_phone, l.bill_contact_email, l.fssai,
    l.latitude, l.longitude, l.carpet_area, l.trading_area,
    l.imp_site_name, l.like_loc_list, l.gst_region_desc,
    l.default_wh AS dc_code, dc.loc_name AS dc_name, dc.bill_city AS dc_city,
    hla.area, hla.area_manager, hla.region, hla.circle, hla.cluster
FROM master.loc l
    LEFT JOIN master.loc dc ON dc.loc = l.default_wh AND dc.loc_type = 'DC'
    LEFT JOIN hrms.hrms_loc_area hla ON hla.loc = l.loc
WHERE l.loc_type IN ('STORE', 'DARKSTORE')
  AND (l.open_date >= CURRENT_DATE - INTERVAL '365 days' OR l.loc IN (1600, 4132));