-- ✅ 正确：明确指定 SRID 和几何类型
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    geom GEOMETRY(Point, 4326) NOT NULL  -- 明确指定类型和 SRID
);

-- ❌ 错误：不指定类型约束
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY  -- 允许任何类型、任何 SRID，后续会出问题
);

-- ❌ 错误：使用错误的类型
CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    geom GEOMETRY(Point, 4326)  -- 道路应该是 LineString 而非 Point
);

CREATE TABLE pois (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category VARCHAR(50) NOT NULL,
    geom GEOMETRY(Point, 4326) NOT NULL,

    -- 空间约束
    CONSTRAINT enforce_srid CHECK (ST_SRID(geom) = 4326),
    CONSTRAINT enforce_valid CHECK (ST_IsValid(geom)),

    -- 坐标范围约束（中国范围）
    CONSTRAINT enforce_x_range CHECK (ST_X(geom) BETWEEN 73 AND 136),
    CONSTRAINT enforce_y_range CHECK (ST_Y(geom) BETWEEN 3 AND 54)
);

-- 添加几何类型约束
ALTER TABLE pois ADD CONSTRAINT enforce_geotype
    CHECK (ST_GeometryType(geom) = 'ST_Point');

-- ✅ 推荐：空间数据与属性数据分离（如果属性经常变化）
CREATE TABLE facilities (
    id SERIAL PRIMARY KEY,
    name TEXT,
    facility_type VARCHAR(50),
    geom GEOMETRY(Point, 4326) NOT NULL
);

CREATE TABLE facility_details (
    facility_id INTEGER PRIMARY KEY REFERENCES facilities(id),
    description TEXT,
    opening_hours JSONB,
    contact_info JSONB,
    last_inspection DATE
);

-- ✅ 也可以：空间数据与属性数据合并（如果访问模式一致）
CREATE TABLE facilities (
    id SERIAL PRIMARY KEY,
    name TEXT,
    facility_type VARCHAR(50),
    description TEXT,
    opening_hours JSONB,
    geom GEOMETRY(Point, 4326) NOT NULL
);

-- 场景 1：局部区域分析 → Geometry + 投影坐标系
CREATE TABLE city_buildings (
    id SERIAL PRIMARY KEY,
    name TEXT,
    height_m NUMERIC,
    geom GEOMETRY(MultiPolygon, 4547) NOT NULL  -- CGCS2000 投影，单位米
);

-- 场景 2：全球范围查询 → Geography
CREATE TABLE global_airports (
    id SERIAL PRIMARY KEY,
    name TEXT,
    iata_code CHAR(3),
    geom GEOMETRY(Point, 4326) NOT NULL,
    geog GEOGRAPHY GENERATED ALWAYS AS (geom::geography) STORED  -- 生成列
);

CREATE INDEX idx_airports_geog ON global_airports USING GIST(geog);

-- 查询时直接使用 geography
SELECT name FROM global_airports
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)::geography, 500000);

-- ✅ 好的实践：一致的格式，注释说明空间操作
-- 查找配送范围内的门店，按距离排序
SELECT
    s.name,
    s.address,
    ROUND(ST_Distance(s.geom::geography, target::geography)) AS distance_m
FROM stores s
WHERE
    -- 利用空间索引先过滤
    s.geom && ST_Expand(target, 0.05)
    -- 精确距离判断
    AND ST_DWithin(s.geom::geography, target::geography, 3000)
    -- 业务条件
    AND s.status = 'active'
ORDER BY s.geom <-> target  -- KNN 排序
LIMIT 20;

-- ❌ 不好的实践：无注释，混合使用函数
SELECT name FROM stores WHERE ST_Distance(geom::geography, target::geography) < 3000 ORDER BY ST_Distance(geom::geography, target::geography) LIMIT 20;

-- 模板 1：附近搜索
WITH target AS (
    SELECT ST_SetSRID(ST_MakePoint(:lng, :lat), 4326) AS geom
)
SELECT
    t.name,
    ROUND(ST_Distance(t.geom::geography, tgt.geom::geography)) AS distance_m
FROM target_table t, target tgt
WHERE
    -- 索引过滤
    t.geom && ST_Expand(tgt.geom, :radius_degrees)
    -- 精确过滤
    AND ST_DWithin(t.geom::geography, tgt.geom::geography, :radius_meters)
    -- 业务过滤
    AND t.status = 'active'
ORDER BY t.geom <-> tgt.geom
LIMIT :limit;

-- 模板 2：区域统计
SELECT
    d.name,
    count(p.*) AS poi_count,
    ST_Area(d.geom::geography) / 1000000 AS area_km2,
    count(p.*) / (ST_Area(d.geom::geography) / 1000000) AS density_per_km2
FROM districts d
LEFT JOIN pois p ON ST_Contains(d.geom, p.geom)
GROUP BY d.id, d.name, d.geom;

-- 模板 3：叠加分析
SELECT
    a.name AS region_a,
    b.name AS region_b,
    ST_Intersection(a.geom, b.geom) AS overlap_geom,
    ST_Area(ST_Intersection(a.geom, b.geom)::geography) / 1000000 AS overlap_km2
FROM regions a
JOIN regions b ON
    a.id < b.id
    AND a.geom && b.geom  -- 索引过滤
    AND ST_Overlaps(a.geom, b.geom);  -- 精确判断

-- 启用 RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- 创建策略：用户只能看到自己部门的数据
CREATE POLICY department_isolation ON sensitive_data
    FOR ALL
    USING (department = current_setting('app.current_department'));

-- 设置会话变量
SET app.current_department = '华东区';
SELECT * FROM sensitive_data;  -- 只返回华东区数据

-- 创建角色
CREATE ROLE gis_reader;
CREATE ROLE gis_writer;
CREATE ROLE gis_admin;

-- 授权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gis_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO gis_writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO gis_admin;

-- 空间函数权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO gis_reader;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO gis_writer;

-- 默认权限（未来创建的表）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO gis_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO gis_writer;

-- 数据模型
CREATE TABLE restaurants (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'active',
    delivery_radius_m INTEGER DEFAULT 3000,
    geom GEOMETRY(Point, 4326) NOT NULL
);

CREATE TABLE delivery_zones (
    id SERIAL PRIMARY KEY,
    restaurant_id INTEGER REFERENCES restaurants(id),
    zone_geom GEOMETRY(Polygon, 4326) NOT NULL,
    priority INTEGER DEFAULT 1
);

CREATE TABLE riders (
    id SERIAL PRIMARY KEY,
    name TEXT,
    phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'offline',
    current_geom GEOMETRY(Point, 4326),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    restaurant_id INTEGER REFERENCES restaurants(id),
    rider_id INTEGER REFERENCES riders(id),
    delivery_address TEXT,
    delivery_geom GEOMETRY(Point, 4326),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 索引
CREATE INDEX idx_restaurants_geom ON restaurants USING GIST(geom);
CREATE INDEX idx_riders_geom ON riders USING GIST(current_geom);
CREATE INDEX idx_orders_geom ON orders USING GIST(delivery_geom);
CREATE INDEX idx_orders_created ON orders(created_at);

-- 查询：找到用户附近的可配送餐厅
SELECT
    r.name,
    r.address,
    ROUND(ST_Distance(r.geom::geography, user_loc::geography)) AS distance_m,
    r.delivery_radius_m
FROM restaurants r, (SELECT ST_SetSRID(ST_MakePoint(:lng, :lat), 4326) AS user_loc) u
WHERE r.status = 'active'
  AND r.geom && ST_Expand(u.user_loc, 0.05)
  AND ST_DWithin(r.geom::geography, u.user_loc::geography, r.delivery_radius_m)
ORDER BY r.geom <-> u.user_loc
LIMIT 20;

-- 查询：为订单分配最近的骑手
SELECT rider_id, name, current_geom
FROM riders
WHERE status = 'idle'
  AND current_geom && ST_Expand(order_geom, 0.03)
  AND ST_DWithin(current_geom::geography, order_geom::geography, 5000)
ORDER BY current_geom <-> order_geom
LIMIT 1;

-- 城市部件管理
CREATE TABLE city_assets (
    id SERIAL PRIMARY KEY,
    asset_code VARCHAR(50) UNIQUE NOT NULL,
    asset_type VARCHAR(50) NOT NULL,  -- 路灯、井盖、垃圾桶等
    status VARCHAR(20) DEFAULT 'normal',
    install_date DATE,
    last_maintenance DATE,
    geom GEOMETRY(Point, 4326) NOT NULL
);

-- 巡检工单
CREATE TABLE inspection_tickets (
    id SERIAL PRIMARY KEY,
    asset_id INTEGER REFERENCES city_assets(id),
    reporter_name TEXT,
    issue_type VARCHAR(50),
    description TEXT,
    severity VARCHAR(20),  -- low/medium/high/critical
    status VARCHAR(20) DEFAULT 'open',
    reported_at TIMESTAMPTZ DEFAULT now(),
    resolved_at TIMESTAMPTZ,
    report_geom GEOMETRY(Point, 4326)  -- 上报位置
);

-- 网格管理
CREATE TABLE grid_cells (
    id SERIAL PRIMARY KEY,
    grid_code VARCHAR(20) UNIQUE,
    grid_level INTEGER,  -- 1=一级网格, 2=二级网格
    manager_name TEXT,
    manager_phone VARCHAR(20),
    geom GEOMETRY(MultiPolygon, 4326) NOT NULL
);

-- 查询：某网格内的未处理工单统计
SELECT
    g.grid_code,
    g.manager_name,
    count(t.*) FILTER (WHERE t.status = 'open') AS open_tickets,
    count(t.*) FILTER (WHERE t.severity = 'critical') AS critical_tickets
FROM grid_cells g
LEFT JOIN inspection_tickets t ON ST_Contains(g.geom, t.report_geom)
WHERE g.grid_level = 1
GROUP BY g.id, g.grid_code, g.manager_name
ORDER BY open_tickets DESC;

-- 查询：距离最近的维护人员
WITH problem_asset AS (
    SELECT geom FROM city_assets WHERE asset_code = 'LG-001234'
)
SELECT
    m.name,
    m.phone,
    ROUND(ST_Distance(m.current_geom::geography, pa.geom::geography)) AS distance_m
FROM maintenance_staff m, problem_asset pa
WHERE m.status = 'available'
ORDER BY m.current_geom <-> pa.geom
LIMIT 3;

-- 地块管理（使用拓扑保证边界一致性）
CREATE TABLE land_parcels (
    id SERIAL PRIMARY KEY,
    parcel_code VARCHAR(50) UNIQUE NOT NULL,
    owner_name TEXT,
    land_use VARCHAR(50),  -- 住宅/商业/工业/农业
    area_m2 NUMERIC(14,2),
    geom GEOMETRY(MultiPolygon, 4547) NOT NULL  -- CGCS2000 投影，单位米
);

CREATE INDEX idx_parcels_geom ON land_parcels USING GIST(geom);
CREATE INDEX idx_parcels_code ON land_parcels(parcel_code);
CREATE INDEX idx_parcels_owner ON land_parcels(owner_name);

-- 产权变更历史
CREATE TABLE ownership_history (
    id SERIAL PRIMARY KEY,
    parcel_id INTEGER REFERENCES land_parcels(id),
    previous_owner TEXT,
    new_owner TEXT,
    change_type VARCHAR(30),  -- 买卖/继承/赠与
    change_date DATE,
    notarized_doc TEXT
);

-- 查询：某人的所有不动产
SELECT
    p.parcel_code,
    p.land_use,
    p.area_m2,
    ST_X(ST_Centroid(ST_Transform(p.geom, 4326))) AS center_lng,
    ST_Y(ST_Centroid(ST_Transform(p.geom, 4326))) AS center_lat
FROM land_parcels p
WHERE p.owner_name = '张三';

-- 查询：某地块的相邻地块
SELECT
    b.parcel_code,
    b.owner_name,
    ST_Length(ST_Intersection(ST_Boundary(a.geom), ST_Boundary(b.geom))) AS shared_boundary_m
FROM land_parcels a, land_parcels b
WHERE a.parcel_code = 'P-2025-00001'
  AND a.id != b.id
  AND ST_Touches(a.geom, b.geom)
ORDER BY shared_boundary_m DESC;

-- 1. 检查数据库大小
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- 2. 检查空间表大小
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 3. 检查无效几何
SELECT table_name, invalid_count
FROM (
    SELECT 'stores' AS table_name, count(*) AS invalid_count FROM stores WHERE NOT ST_IsValid(geom)
    UNION ALL
    SELECT 'roads', count(*) FROM roads WHERE NOT ST_IsValid(geom)
    UNION ALL
    SELECT 'districts', count(*) FROM districts WHERE NOT ST_IsValid(geom)
) t
WHERE invalid_count > 0;

-- 4. 检查索引使用情况
SELECT
    indexrelname,
    idx_scan AS times_used,
    idx_tup_read AS rows_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- 从未使用的索引
  AND indexrelname LIKE 'idx_%';

-- 5. 检查长事务
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- 每周维护任务
-- 1. 更新统计信息
ANALYZE stores;
ANALYZE roads;
ANALYZE districts;

-- 2. 清理死行
VACUUM (VERBOSE, ANALYZE) stores;
VACUUM (VERBOSE, ANALYZE) roads;

-- 3. 重建膨胀索引（如果需要）
REINDEX INDEX CONCURRENTLY idx_stores_geom;

-- 4. 检查备份完整性
-- （在备份服务器上恢复测试）

-- 查看实际存储大小
SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relname IN ('stores', 'roads', 'districts');