-- 连锁餐饮门店选址分析
-- 基准期：2026年4月
-- 数据：经营、菜品、会员、平台、成本、库存、面积、店龄、租约及精确坐标

CREATE OR REPLACE VIEW analytics.v_store_site_profile_april AS
SELECT a.*,
       CASE
         WHEN a.business_type='特殊业态' THEN '特殊业态'
         WHEN a.business_address ~ '机场|航站楼' THEN '交通枢纽'
         WHEN a.business_address ~ '大学|食堂|档口' THEN '校园档口'
         WHEN a.business_address ~ '总部|科技园|产业园|创业园|商务楼|写字楼|信息产业基地|生命科学园|自贸试验区|经海|荣华' THEN '办公园区'
         WHEN a.business_address ~ '商场|商城|购物|超市|万科|龙湖|大悦|搜秀|美食城|商业大厦|商铺' THEN '商场商业体'
         WHEN a.business_address ~ '社区|小区|家园|里|园一区|园东街' THEN '社区居民'
         ELSE '街边综合'
       END AS site_scene,
       CASE
         WHEN a.business_address ~ '地下一层|负一层|-1层|-1至|B1|b1' THEN '地下层'
         WHEN a.business_address ~ '二层|2层|四层|4层|4F|五层|5层|23层' THEN '非首层'
         WHEN a.business_address ~ '一层|1层|底商' THEN '首层'
         ELSE '楼层不明'
       END AS floor_type,
       CASE
         WHEN a.area_sqm IS NULL THEN '面积缺失'
         WHEN a.area_sqm<=180 THEN '≤180㎡'
         WHEN a.area_sqm<=250 THEN '181-250㎡'
         WHEN a.area_sqm<=350 THEN '251-350㎡'
         WHEN a.area_sqm<=500 THEN '351-500㎡'
         ELSE '>500㎡'
       END AS area_band,
       CASE
         WHEN a.store_age_years IS NULL THEN '店龄缺失'
         WHEN a.store_age_years<1 THEN '新店<1年'
         WHEN a.store_age_years<3 THEN '成长期1-3年'
         WHEN a.store_age_years<8 THEN '成熟期3-8年'
         ELSE '老店≥8年'
       END AS age_band
FROM analytics.v_store_area_efficiency_april a;

CREATE OR REPLACE VIEW analytics.v_store_spatial_pairs_april AS
WITH physical AS (
    SELECT store_code,store_name,district,site_scene,action_priority,received,
           monthly_received_per_sqm,repeat_rate_pct,actual_food_cost_rate_pct,
           latitude_gcj02 AS lat,longitude_gcj02 AS lon
    FROM analytics.v_store_site_profile_april
    WHERE latitude_gcj02 IS NOT NULL AND longitude_gcj02 IS NOT NULL
), pairs AS (
    SELECT a.store_code AS store_code_a,a.store_name AS store_name_a,
           b.store_code AS store_code_b,b.store_name AS store_name_b,
           a.district AS district_a,b.district AS district_b,
           a.site_scene AS scene_a,b.site_scene AS scene_b,
           a.action_priority AS priority_a,b.action_priority AS priority_b,
           a.received AS received_a,b.received AS received_b,
           a.monthly_received_per_sqm AS sqm_efficiency_a,
           b.monthly_received_per_sqm AS sqm_efficiency_b,
           6371 * acos(least(1.0,greatest(-1.0,
             cos(radians(a.lat))*cos(radians(b.lat))*cos(radians(b.lon-a.lon))+
             sin(radians(a.lat))*sin(radians(b.lat))
           ))) AS distance_km
    FROM physical a
    JOIN physical b ON a.store_code<b.store_code
)
SELECT *,
       CASE WHEN distance_km<1 THEN '高度重叠<1km'
            WHEN distance_km<2 THEN '较高重叠1-2km'
            WHEN distance_km<3 THEN '观察2-3km'
            ELSE '相对独立≥3km' END AS proximity_level
FROM pairs;

CREATE OR REPLACE VIEW analytics.v_store_nearest_neighbor_april AS
WITH directed AS (
    SELECT store_code_a AS store_code,store_name_a AS store_name,
           store_code_b AS nearest_store_code,store_name_b AS nearest_store_name,
           distance_km
    FROM analytics.v_store_spatial_pairs_april
    UNION ALL
    SELECT store_code_b,store_name_b,store_code_a,store_name_a,distance_km
    FROM analytics.v_store_spatial_pairs_april
), ranked AS (
    SELECT *,row_number() OVER(PARTITION BY store_code ORDER BY distance_km) AS rn
    FROM directed
)
SELECT store_code,store_name,nearest_store_code,nearest_store_name,
       round(distance_km::numeric,2) AS nearest_distance_km,
       CASE WHEN distance_km<1 THEN '高度重叠<1km'
            WHEN distance_km<2 THEN '较高重叠1-2km'
            WHEN distance_km<3 THEN '观察2-3km'
            ELSE '相对独立≥3km' END AS nearest_proximity_level
FROM ranked WHERE rn=1;

CREATE OR REPLACE VIEW analytics.v_site_segment_benchmark_april AS
SELECT site_scene,area_band,count(*) AS store_count,
       round(avg(area_sqm)::numeric,1) AS avg_area_sqm,
       round(avg(received)::numeric,2) AS avg_received,
       round(percentile_cont(0.5) WITHIN GROUP(ORDER BY received)::numeric,2) AS median_received,
       round(avg(monthly_received_per_sqm)::numeric,2) AS avg_received_per_sqm,
       round(percentile_cont(0.5) WITHIN GROUP(ORDER BY monthly_received_per_sqm)::numeric,2) AS median_received_per_sqm,
       round(avg(avg_bill_value)::numeric,2) AS avg_bill_value,
       round(avg(discount_rate_pct)::numeric,2) AS avg_discount_rate_pct,
       round(avg(repeat_rate_pct)::numeric,2) AS avg_repeat_rate_pct,
       round(avg(actual_food_cost_rate_pct) FILTER(WHERE comparison_status='可比')::numeric,2) AS avg_actual_cost_rate_pct,
       round(avg(delivery_bill_share_pct)::numeric,2) AS avg_delivery_share_pct,
       round(avg(noodle_drink_attach_pct)::numeric,2) AS avg_drink_attach_pct
FROM analytics.v_store_site_profile_april
WHERE business_type='标准门店' AND received>0 AND area_sqm IS NOT NULL
GROUP BY site_scene,area_band;

CREATE OR REPLACE VIEW analytics.v_district_site_benchmark_april AS
SELECT city,district,count(*) AS store_count,
       round(avg(area_sqm)::numeric,1) AS avg_area_sqm,
       round(sum(received)::numeric,2) AS total_received,
       round(avg(received)::numeric,2) AS avg_received,
       round(percentile_cont(0.5) WITHIN GROUP(ORDER BY received)::numeric,2) AS median_received,
       round(avg(monthly_received_per_sqm)::numeric,2) AS avg_received_per_sqm,
       round(avg(avg_bill_value)::numeric,2) AS avg_bill_value,
       round(avg(discount_rate_pct)::numeric,2) AS avg_discount_rate_pct,
       round(avg(repeat_rate_pct)::numeric,2) AS avg_repeat_rate_pct,
       round(avg(actual_food_cost_rate_pct) FILTER(WHERE comparison_status='可比')::numeric,2) AS avg_actual_cost_rate_pct,
       round(avg(combined_platform_cost_rate_pct)::numeric,2) AS avg_platform_cost_rate_pct,
       count(*) FILTER(WHERE action_priority LIKE 'P0%') AS p0_count,
       count(*) FILTER(WHERE action_priority='P1-重点整改') AS p1_count
FROM analytics.v_store_site_profile_april
WHERE business_type='标准门店' AND received>0
GROUP BY city,district;

CREATE OR REPLACE VIEW analytics.v_store_site_replication_score_april AS
WITH eligible AS (
    SELECT p.*,n.nearest_store_code,n.nearest_store_name,n.nearest_distance_km,
           percent_rank() OVER(ORDER BY p.monthly_received_per_sqm) AS sqm_score,
           percent_rank() OVER(ORDER BY p.avg_daily_received) AS daily_score,
           percent_rank() OVER(ORDER BY p.repeat_rate_pct NULLS FIRST) AS repeat_score,
           1-percent_rank() OVER(ORDER BY p.discount_rate_pct) AS discount_score,
           1-percent_rank() OVER(ORDER BY p.actual_food_cost_rate_pct NULLS LAST) AS cost_score,
           1-percent_rank() OVER(ORDER BY p.combined_platform_cost_rate_pct NULLS LAST) AS platform_score,
           greatest(0,1-p.problem_count/6.0) AS execution_score
    FROM analytics.v_store_site_profile_april p
    LEFT JOIN analytics.v_store_nearest_neighbor_april n USING(store_code,store_name)
    WHERE p.business_type='标准门店' AND p.received>0 AND p.area_sqm IS NOT NULL
), scored AS (
    SELECT *,round((
        0.30*sqm_score+0.20*daily_score+0.15*repeat_score+
        0.10*discount_score+0.10*cost_score+0.10*platform_score+
        0.05*execution_score
    )::numeric*100,2) AS site_replication_score
    FROM eligible
)
SELECT *,
       CASE
         WHEN site_replication_score>=75 AND problem_count<=1 THEN '优先提炼选址原型'
         WHEN site_replication_score>=60 THEN '可作为同类参考'
         WHEN site_replication_score<40 THEN '不宜作为选址标杆'
         ELSE '观察验证'
       END AS replication_recommendation,
       CASE
         WHEN nearest_distance_km>=3 AND site_replication_score>=70 THEN '高表现且周边相对独立，可研究相似商圈扩张'
         WHEN nearest_distance_km<1.5 THEN '邻店较近，新址需重点防止同店分流'
         ELSE '常规评估'
       END AS spatial_recommendation
FROM scored;

CREATE OR REPLACE VIEW analytics.v_store_location_overlap_risk_april AS
SELECT p.*,
       a.problem_count AS problem_count_a,b.problem_count AS problem_count_b,
       CASE
         WHEN p.distance_km<1 AND (a.action_priority LIKE 'P0%' OR b.action_priority LIKE 'P0%' OR a.action_priority='P1-重点整改' OR b.action_priority='P1-重点整改')
           THEN '高风险：距离近且至少一家经营承压'
         WHEN p.distance_km<1.5 THEN '中风险：需核查客群和配送圈重叠'
         ELSE '观察'
       END AS overlap_risk
FROM analytics.v_store_spatial_pairs_april p
JOIN analytics.v_store_site_profile_april a ON a.store_code=p.store_code_a
JOIN analytics.v_store_site_profile_april b ON b.store_code=p.store_code_b
WHERE p.distance_km<3;

-- 典型结果查询
SELECT site_scene,count(*) store_count,round(avg(received)::numeric,0) avg_received,
       round(avg(monthly_received_per_sqm)::numeric,2) avg_received_per_sqm
FROM analytics.v_store_site_profile_april
WHERE business_type='标准门店' AND received>0 AND area_sqm IS NOT NULL
GROUP BY site_scene ORDER BY avg_received_per_sqm DESC;

SELECT store_name,site_scene,area_sqm,received,monthly_received_per_sqm,
       nearest_store_name,nearest_distance_km,site_replication_score,
       replication_recommendation,spatial_recommendation
FROM analytics.v_store_site_replication_score_april
ORDER BY site_replication_score DESC LIMIT 20;

SELECT store_name_a,store_name_b,round(distance_km::numeric,2) distance_km,
       received_a,received_b,overlap_risk
FROM analytics.v_store_location_overlap_risk_april
ORDER BY distance_km LIMIT 30;