ctc_poc2026/POC-TSG匹配测试用例_DMK库表DDL(1).sql

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE SCHEMA IF NOT EXISTS dmk;

-- =========================================================
-- 1. 通用维度与配置表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.td_account_period (
    data_type integer NOT NULL,
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    is_current boolean NOT NULL DEFAULT false,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (data_type, year_month)
);

COMMENT ON TABLE dmk.td_account_period IS '可查询账期维表，支撑 /api/common/account-periods。';
COMMENT ON COLUMN dmk.td_account_period.data_type IS '数据来源类型，对应字典 data_type，区分 OTT/工参/用户等不同数据源';
COMMENT ON COLUMN dmk.td_account_period.year_month IS '账期，格式 YYYY-MM，统一月粒度';
COMMENT ON COLUMN dmk.td_account_period.year IS '账期年份，冗余于 year_month';
COMMENT ON COLUMN dmk.td_account_period.month IS '账期月份(1-12)，冗余于 year_month';
COMMENT ON COLUMN dmk.td_account_period.is_current IS '是否当前最新账期，true 表示该数据源当前默认账期';
COMMENT ON COLUMN dmk.td_account_period.is_valid IS '是否有效，1=有效 0=无效（逻辑删除）';
COMMENT ON COLUMN dmk.td_account_period.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_region (
    region_code integer PRIMARY KEY,
    region_name varchar(64) NOT NULL,
    region_level varchar(16) NOT NULL,
    parent_region_code integer,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    region_wkt text,
    region_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN region_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(region_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    sort_no integer NOT NULL DEFAULT 0,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    CHECK (region_level IN ('nation', 'province', 'city', 'district'))
);

COMMENT ON TABLE dmk.td_region IS '行政区域维表，保留区域树、区域 WKT 和区域空间索引字段。';
COMMENT ON COLUMN dmk.td_region.region_code IS '区域编码，全国/省/市/区县统一主键';
COMMENT ON COLUMN dmk.td_region.region_name IS '区域名称';
COMMENT ON COLUMN dmk.td_region.region_level IS '区域级别：nation/province/city/district';
COMMENT ON COLUMN dmk.td_region.parent_region_code IS '父级区域编码，用于构造区域树';
COMMENT ON COLUMN dmk.td_region.provincecode IS '省编码（冗余）';
COMMENT ON COLUMN dmk.td_region.province_name IS '省名称（冗余）';
COMMENT ON COLUMN dmk.td_region.citycode IS '地市编码（冗余）';
COMMENT ON COLUMN dmk.td_region.city_name IS '地市名称（冗余）';
COMMENT ON COLUMN dmk.td_region.districtcode IS '区县编码（冗余）';
COMMENT ON COLUMN dmk.td_region.district_name IS '区县名称（冗余）';
COMMENT ON COLUMN dmk.td_region.center_lon IS '区域中心点经度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_region.center_lat IS '区域中心点纬度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_region.bbox IS '区域外接矩形 [minLon,minLat,maxLon,maxLat]，便于地图视野初始化';
COMMENT ON COLUMN dmk.td_region.region_wkt IS '区域多边形 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_region.region_geom IS '由 region_wkt 生成的 MultiPolygon 几何列，用于 GiST 空间索引和空间过滤';
COMMENT ON COLUMN dmk.td_region.sort_no IS '区域显示排序号';
COMMENT ON COLUMN dmk.td_region.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_region.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_region_parent ON dmk.td_region(parent_region_code, region_level, sort_no);
CREATE INDEX IF NOT EXISTS idx_td_region_geom ON dmk.td_region USING gist(region_geom) WHERE region_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_dict_item (
    dict_type varchar(64) NOT NULL,
    dict_code varchar(64) NOT NULL,
    dict_name varchar(128) NOT NULL,
    dict_desc text,
    sort_no integer NOT NULL DEFAULT 0,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (dict_type, dict_code)
);

COMMENT ON TABLE dmk.td_dict_item IS '通用字典维表，覆盖 data_type、operator_name、network_class、scene_type 等枚举。';
COMMENT ON COLUMN dmk.td_dict_item.dict_type IS '字典类型编码，如 data_type、operator_name、network_class、scene_type 等';
COMMENT ON COLUMN dmk.td_dict_item.dict_code IS '字典项编码';
COMMENT ON COLUMN dmk.td_dict_item.dict_name IS '字典项名称（用于前端展示）';
COMMENT ON COLUMN dmk.td_dict_item.dict_desc IS '字典项描述/补充说明；初始化字典中同类型记录可统一保存字典类型名称，供 /api/common/dict/types 返回';
COMMENT ON COLUMN dmk.td_dict_item.sort_no IS '同类型下的排序号';
COMMENT ON COLUMN dmk.td_dict_item.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_dict_item.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_metric_definition (
    module_code varchar(64) NOT NULL,
    metric_code varchar(64) NOT NULL,
    threshold varchar(32) NOT NULL DEFAULT '',
    metric_group varchar(64),
    metric_name varchar(128) NOT NULL,
    metric_desc text,
    formula text,
    source_table varchar(128),
    unit varchar(32),
    is_default boolean NOT NULL DEFAULT false,
    sort_no integer NOT NULL DEFAULT 0,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (module_code, metric_code, threshold)
);

COMMENT ON TABLE dmk.td_metric_definition IS '指标口径维表，threshold 用于区分 -105/-110 等不同覆盖口径。';
COMMENT ON COLUMN dmk.td_metric_definition.module_code IS '指标所属模块编码（如 grid、building、scene、cluster、poor 等）';
COMMENT ON COLUMN dmk.td_metric_definition.metric_code IS '指标编码（如 mr_cover_rate、avgrsrp 等）';
COMMENT ON COLUMN dmk.td_metric_definition.threshold IS '阈值标识，如 -105/-110，用于区分同名指标的不同口径，无阈值时为空串';
COMMENT ON COLUMN dmk.td_metric_definition.metric_group IS '指标分组（如 coverage、user、quality 等）';
COMMENT ON COLUMN dmk.td_metric_definition.metric_name IS '指标显示名称';
COMMENT ON COLUMN dmk.td_metric_definition.metric_desc IS '指标说明/口径描述（用于报表口径弹窗）';
COMMENT ON COLUMN dmk.td_metric_definition.formula IS '计算公式描述';
COMMENT ON COLUMN dmk.td_metric_definition.source_table IS '指标来源表说明';
COMMENT ON COLUMN dmk.td_metric_definition.unit IS '指标单位（如 %、dBm、个、GB 等）';
COMMENT ON COLUMN dmk.td_metric_definition.is_default IS '是否为模块默认展示指标';
COMMENT ON COLUMN dmk.td_metric_definition.sort_no IS '同模块下的排序号';
COMMENT ON COLUMN dmk.td_metric_definition.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_metric_definition.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_layer_config (
    layer_type varchar(32) NOT NULL,
    layer_role varchar(32) NOT NULL DEFAULT 'base',
    render_mode varchar(16) NOT NULL DEFAULT '2D',
    workspace varchar(64) NOT NULL DEFAULT 'ott',
    layer_name varchar(128) NOT NULL,
    default_style_name varchar(128) NOT NULL,
    wms_url varchar(256) NOT NULL DEFAULT '/geoserver/ott/wms',
    wms_version varchar(16) NOT NULL DEFAULT '1.1.1',
    wms_format varchar(64) NOT NULL DEFAULT 'image/png',
    wms_transparent boolean NOT NULL DEFAULT true,
    wms_srs varchar(32) NOT NULL DEFAULT 'EPSG:4326',
    wms_tiled boolean NOT NULL DEFAULT true,
    wms_tile_size integer NOT NULL DEFAULT 256,
    wms_min_zoom integer NOT NULL DEFAULT 8,
    wms_max_zoom integer NOT NULL DEFAULT 19,
    wms_opacity numeric(4, 2) NOT NULL DEFAULT 0.9,
    geom_column varchar(64) NOT NULL,
    cql_template text,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (layer_type, layer_role, render_mode)
);

COMMENT ON TABLE dmk.td_layer_config IS 'GeoServer WMS 图层配置表，Java 服务基于该表和白名单字段生成 layer_config。';
COMMENT ON COLUMN dmk.td_layer_config.layer_type IS '图层类型（building/grid/scene/cell/cluster/poor 等）';
COMMENT ON COLUMN dmk.td_layer_config.layer_role IS '图层角色：base=基础图层 view=视图层 publish=发布层';
COMMENT ON COLUMN dmk.td_layer_config.render_mode IS '渲染模式：2D / 3D';
COMMENT ON COLUMN dmk.td_layer_config.workspace IS 'GeoServer 工作空间，默认 ott';
COMMENT ON COLUMN dmk.td_layer_config.layer_name IS 'GeoServer 图层名称（workspace:layer 中的 layer 部分）';
COMMENT ON COLUMN dmk.td_layer_config.default_style_name IS '默认 SLD 样式名称';
COMMENT ON COLUMN dmk.td_layer_config.wms_url IS 'WMS 服务地址，默认 /geoserver/ott/wms';
COMMENT ON COLUMN dmk.td_layer_config.wms_version IS 'WMS 协议版本，默认 1.1.1';
COMMENT ON COLUMN dmk.td_layer_config.wms_format IS 'WMS 图片格式，默认 image/png';
COMMENT ON COLUMN dmk.td_layer_config.wms_transparent IS '是否透明背景，默认 true';
COMMENT ON COLUMN dmk.td_layer_config.wms_srs IS '坐标参考系，默认 EPSG:4326';
COMMENT ON COLUMN dmk.td_layer_config.wms_tiled IS '是否使用瓦片模式，默认 true';
COMMENT ON COLUMN dmk.td_layer_config.wms_tile_size IS '瓦片尺寸（像素），默认 256';
COMMENT ON COLUMN dmk.td_layer_config.wms_min_zoom IS '图层最小缩放级别，默认 8';
COMMENT ON COLUMN dmk.td_layer_config.wms_max_zoom IS '图层最大缩放级别，默认 19';
COMMENT ON COLUMN dmk.td_layer_config.wms_opacity IS '图层默认透明度（0.0~1.0），默认 0.9';
COMMENT ON COLUMN dmk.td_layer_config.geom_column IS '发布所用几何列名（如 grid_geom/aoi_geom），视图层可统一别名为 geom';
COMMENT ON COLUMN dmk.td_layer_config.cql_template IS 'CQL 过滤模板，由 Java 服务基于白名单字段拼装时填充';
COMMENT ON COLUMN dmk.td_layer_config.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_layer_config.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_layer_metric (
    layer_type varchar(32) NOT NULL,
    metric_code varchar(64) NOT NULL,
    metric_name varchar(128) NOT NULL,
    default_style_name varchar(128) NOT NULL,
    supported_operator varchar(32)[] NOT NULL DEFAULT ARRAY[]::varchar[],
    supported_network_class varchar(32)[] NOT NULL DEFAULT ARRAY[]::varchar[],
    sort_no integer NOT NULL DEFAULT 0,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (layer_type, metric_code)
);

COMMENT ON TABLE dmk.td_layer_metric IS '图层指标白名单配置表，支撑 /api/common/layer-metrics。';
COMMENT ON COLUMN dmk.td_layer_metric.layer_type IS '图层类型，对应 td_layer_config.layer_type';
COMMENT ON COLUMN dmk.td_layer_metric.metric_code IS '指标编码，对应 td_metric_definition.metric_code';
COMMENT ON COLUMN dmk.td_layer_metric.metric_name IS '指标显示名称';
COMMENT ON COLUMN dmk.td_layer_metric.default_style_name IS '该指标默认 SLD 样式名称';
COMMENT ON COLUMN dmk.td_layer_metric.supported_operator IS '支持的运营商列表（数组），为空表示全部';
COMMENT ON COLUMN dmk.td_layer_metric.supported_network_class IS '支持的网络制式列表（数组），如 4G/5G_SA/all';
COMMENT ON COLUMN dmk.td_layer_metric.sort_no IS '同图层下指标排序号';
COMMENT ON COLUMN dmk.td_layer_metric.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_layer_metric.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_layer_legend (
    legend_id varchar(64) PRIMARY KEY,
    account_id varchar(64) NOT NULL,
    layer_type varchar(32) NOT NULL,
    metric_code varchar(64) NOT NULL,
    legend_name varchar(128) NOT NULL,
    ranges jsonb NOT NULL,
    is_default boolean NOT NULL DEFAULT false,
    updated_by varchar(64),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    is_deleted smallint NOT NULL DEFAULT 0
);

COMMENT ON TABLE dmk.td_layer_legend IS '账号维度自定义图例表，ranges 保存颜色、区间、标签配置。';
COMMENT ON COLUMN dmk.td_layer_legend.legend_id IS '图例主键 ID';
COMMENT ON COLUMN dmk.td_layer_legend.account_id IS '账号 ID（自定义图例归属用户）';
COMMENT ON COLUMN dmk.td_layer_legend.layer_type IS '图层类型';
COMMENT ON COLUMN dmk.td_layer_legend.metric_code IS '指标编码';
COMMENT ON COLUMN dmk.td_layer_legend.legend_name IS '图例名称';
COMMENT ON COLUMN dmk.td_layer_legend.ranges IS '图例配置 JSON，包含颜色、区间、标签等';
COMMENT ON COLUMN dmk.td_layer_legend.is_default IS '是否为该用户默认图例';
COMMENT ON COLUMN dmk.td_layer_legend.updated_by IS '最后更新人账号';
COMMENT ON COLUMN dmk.td_layer_legend.updated_time IS '记录更新时间';
COMMENT ON COLUMN dmk.td_layer_legend.is_deleted IS '是否已删除：0=未删除 1=已删除（逻辑删除）';
CREATE INDEX IF NOT EXISTS idx_td_layer_legend_query ON dmk.td_layer_legend(account_id, layer_type, metric_code, is_deleted);

-- =========================================================
-- 2. 空间基础维度表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.td_grid (
    regionid varchar(64) NOT NULL,
    x_offset_20 varchar(32) NOT NULL,
    y_offset_20 varchar(32) NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    grid_wkt text,
    grid_geom geometry(Polygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN grid_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(grid_wkt, 4326)::geometry(Polygon, 4326)
        END
    ) STORED,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (regionid, x_offset_20, y_offset_20)
);

COMMENT ON TABLE dmk.td_grid IS '栅格基础维表，保留 WKT 和生成 geometry 用于空间关联。';
COMMENT ON COLUMN dmk.td_grid.regionid IS '栅格所属区域 ID';
COMMENT ON COLUMN dmk.td_grid.x_offset_20 IS '栅格 X 偏移（20m 网格编码）';
COMMENT ON COLUMN dmk.td_grid.y_offset_20 IS '栅格 Y 偏移（20m 网格编码）';
COMMENT ON COLUMN dmk.td_grid.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_grid.province_name IS '省名称';
COMMENT ON COLUMN dmk.td_grid.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_grid.city_name IS '地市名称';
COMMENT ON COLUMN dmk.td_grid.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_grid.district_name IS '区县名称';
COMMENT ON COLUMN dmk.td_grid.center_lon IS '栅格中心点经度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_grid.center_lat IS '栅格中心点纬度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_grid.grid_wkt IS '栅格多边形 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_grid.grid_geom IS '由 grid_wkt 生成的 Polygon 几何列，用于 GiST 空间索引';
COMMENT ON COLUMN dmk.td_grid.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_grid.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_grid_region ON dmk.td_grid(provincecode, citycode, districtcode);
CREATE INDEX IF NOT EXISTS idx_td_grid_geom ON dmk.td_grid USING gist(grid_geom) WHERE grid_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_cell_param_m (
    year_month varchar(7) NOT NULL,
    data_type integer,
    operator_name varchar(32),
    network_class varchar(32) NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    cellkey varchar(64) NOT NULL,
    cell_name varchar(128),
    cell_lon numeric(10, 6),
    cell_lat numeric(10, 6),
    cell_wkt text,
    cell_geom geometry(Point, 4326) GENERATED ALWAYS AS (
        CASE WHEN cell_wkt IS NOT NULL THEN ST_GeomFromText(cell_wkt, 4326)::geometry(Point, 4326)
             WHEN cell_lon IS NOT NULL AND cell_lat IS NOT NULL THEN ST_SetSRID(ST_MakePoint(cell_lon, cell_lat), 4326)::geometry(Point, 4326)
             ELSE NULL
        END
    ) STORED,
    cell_regionid varchar(64),
    cell_x_offset_20 varchar(32),
    cell_y_offset_20 varchar(32),
    pci varchar(32),
    indoor_flag smallint,
    azimuth integer,
    freq varchar(32),
    freq_1 varchar(32),
    vendor varchar(64),
    antenna_height numeric(10, 2),
    mechanical_downdip numeric(10, 2),
    electron_downdip numeric(10, 2),
    cover_type varchar(64),
    rspower numeric(12, 4),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, cellkey, network_class)
);

COMMENT ON TABLE dmk.td_cell_param_m IS '月粒度工参维表，保留小区基础信息、经纬度和 WKT。';
COMMENT ON COLUMN dmk.td_cell_param_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.td_cell_param_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.td_cell_param_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.td_cell_param_m.network_class IS '网络制式（4G/5G_SA/wifi 等）';
COMMENT ON COLUMN dmk.td_cell_param_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_cell_param_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.td_cell_param_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_cell_param_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.td_cell_param_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_cell_param_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.td_cell_param_m.cellkey IS '小区唯一键 cellkey';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_name IS '小区名称';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_lon IS '小区经度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_lat IS '小区纬度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_wkt IS '小区点 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_geom IS '由 cell_wkt 或经纬度生成的 Point 几何列，用于 GiST 空间索引';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_regionid IS '小区所属栅格区域 ID';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_x_offset_20 IS '小区所属栅格 X 偏移';
COMMENT ON COLUMN dmk.td_cell_param_m.cell_y_offset_20 IS '小区所属栅格 Y 偏移';
COMMENT ON COLUMN dmk.td_cell_param_m.pci IS '物理小区标识 PCI';
COMMENT ON COLUMN dmk.td_cell_param_m.indoor_flag IS '室内外标识：0=室外 1=室内 -1=未知/全部';
COMMENT ON COLUMN dmk.td_cell_param_m.azimuth IS '天线方位角（度）';
COMMENT ON COLUMN dmk.td_cell_param_m.freq IS '频段标识';
COMMENT ON COLUMN dmk.td_cell_param_m.freq_1 IS '辅助频段标识';
COMMENT ON COLUMN dmk.td_cell_param_m.vendor IS '设备厂家';
COMMENT ON COLUMN dmk.td_cell_param_m.antenna_height IS '天线挂高（米）';
COMMENT ON COLUMN dmk.td_cell_param_m.mechanical_downdip IS '机械下倾角（度）';
COMMENT ON COLUMN dmk.td_cell_param_m.electron_downdip IS '电子下倾角（度）';
COMMENT ON COLUMN dmk.td_cell_param_m.cover_type IS '覆盖类型（如室分、宏站、微站等）';
COMMENT ON COLUMN dmk.td_cell_param_m.rspower IS '参考信号发射功率 RSPower';
COMMENT ON COLUMN dmk.td_cell_param_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_cell_param_region ON dmk.td_cell_param_m(year_month, provincecode, citycode, districtcode, operator_name, network_class);
CREATE INDEX IF NOT EXISTS idx_td_cell_param_geom ON dmk.td_cell_param_m USING gist(cell_geom) WHERE cell_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_building (
    building_id varchar(64) PRIMARY KEY,
    building_name varchar(128) NOT NULL,
    building_type varchar(64),
    building_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    height numeric(12, 2),
    floor_count integer,
    building_area numeric(18, 4),
    population_density numeric(14, 4),
    aoi_wkt text,
    aoi_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN aoi_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(aoi_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now()
);

COMMENT ON TABLE dmk.td_building IS '楼宇基础维表，保留楼宇分类、人口密度、AOI WKT 和 bbox。';
COMMENT ON COLUMN dmk.td_building.building_id IS '楼宇唯一 ID';
COMMENT ON COLUMN dmk.td_building.building_name IS '楼宇名称';
COMMENT ON COLUMN dmk.td_building.building_type IS '楼宇类型编码';
COMMENT ON COLUMN dmk.td_building.building_type_name IS '楼宇类型名称';
COMMENT ON COLUMN dmk.td_building.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_building.province_name IS '省名称';
COMMENT ON COLUMN dmk.td_building.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_building.city_name IS '地市名称';
COMMENT ON COLUMN dmk.td_building.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_building.district_name IS '区县名称';
COMMENT ON COLUMN dmk.td_building.center_lon IS '楼宇中心点经度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_building.center_lat IS '楼宇中心点纬度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_building.bbox IS '楼宇外接矩形 [minLon,minLat,maxLon,maxLat]';
COMMENT ON COLUMN dmk.td_building.height IS '楼宇高度（米）';
COMMENT ON COLUMN dmk.td_building.floor_count IS '楼层数';
COMMENT ON COLUMN dmk.td_building.building_area IS '楼宇面积（平方米，按业务口径可表示建筑面积或占地面积）';
COMMENT ON COLUMN dmk.td_building.population_density IS '人口密度（人/平方公里或人/平方米，按业务口径）';
COMMENT ON COLUMN dmk.td_building.aoi_wkt IS '楼宇 AOI WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_building.aoi_geom IS '由 aoi_wkt 生成的 MultiPolygon 几何列，用于 GiST 空间索引';
COMMENT ON COLUMN dmk.td_building.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_building.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_building_query ON dmk.td_building(provincecode, citycode, districtcode, building_type, is_valid);
CREATE INDEX IF NOT EXISTS idx_td_building_geom ON dmk.td_building USING gist(aoi_geom) WHERE aoi_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_scene (
    scene_id varchar(64) PRIMARY KEY,
    scene_name varchar(128) NOT NULL,
    scene_type varchar(64) NOT NULL,
    scene_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    area_size numeric(18, 4),
    aoi_wkt text,
    aoi_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN aoi_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(aoi_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    is_valid smallint NOT NULL DEFAULT 1,
    updated_time timestamp without time zone NOT NULL DEFAULT now()
);

COMMENT ON TABLE dmk.td_scene IS '重点场景维表，保留场景类型、AOI WKT、中心点和 bbox。';
COMMENT ON COLUMN dmk.td_scene.scene_id IS '场景唯一 ID';
COMMENT ON COLUMN dmk.td_scene.scene_name IS '场景名称';
COMMENT ON COLUMN dmk.td_scene.scene_type IS '场景类型编码';
COMMENT ON COLUMN dmk.td_scene.scene_type_name IS '场景类型名称';
COMMENT ON COLUMN dmk.td_scene.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_scene.province_name IS '省名称';
COMMENT ON COLUMN dmk.td_scene.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_scene.city_name IS '地市名称';
COMMENT ON COLUMN dmk.td_scene.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_scene.district_name IS '区县名称';
COMMENT ON COLUMN dmk.td_scene.center_lon IS '场景中心点经度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_scene.center_lat IS '场景中心点纬度（EPSG:4326）';
COMMENT ON COLUMN dmk.td_scene.bbox IS '场景外接矩形 [minLon,minLat,maxLon,maxLat]';
COMMENT ON COLUMN dmk.td_scene.area_size IS '场景面积（平方米）';
COMMENT ON COLUMN dmk.td_scene.aoi_wkt IS '场景 AOI WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_scene.aoi_geom IS '由 aoi_wkt 生成的 MultiPolygon 几何列，用于 GiST 空间索引';
COMMENT ON COLUMN dmk.td_scene.is_valid IS '是否有效，1=有效 0=无效';
COMMENT ON COLUMN dmk.td_scene.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_scene_search ON dmk.td_scene(provincecode, citycode, scene_type, scene_name, is_valid);
CREATE INDEX IF NOT EXISTS idx_td_scene_geom ON dmk.td_scene USING gist(aoi_geom) WHERE aoi_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_cluster_threshold (
    account_id varchar(64) NOT NULL,
    cluster_type varchar(64) NOT NULL,
    network_class varchar(32) NOT NULL,
    min_grid_count integer NOT NULL DEFAULT 5,
    max_area_size numeric(18, 4),
    min_weighted_score numeric(10, 4),
    max_region_distance numeric(18, 4),
    rsrp_threshold numeric(10, 4),
    coverage_threshold numeric(10, 4),
    threshold_config jsonb,
    updated_by varchar(64),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (account_id, cluster_type, network_class)
);

COMMENT ON TABLE dmk.td_cluster_threshold IS '聚类阈值配置表，支撑在线调整区域栅格数和区域范围阈值。';
COMMENT ON COLUMN dmk.td_cluster_threshold.account_id IS '账号 ID（按用户维度持久化阈值）';
COMMENT ON COLUMN dmk.td_cluster_threshold.cluster_type IS '聚类类型（如弱覆盖、超忙、综合质差等）';
COMMENT ON COLUMN dmk.td_cluster_threshold.network_class IS '网络制式';
COMMENT ON COLUMN dmk.td_cluster_threshold.min_grid_count IS '区域最少栅格数阈值';
COMMENT ON COLUMN dmk.td_cluster_threshold.max_area_size IS '区域最大面积阈值（平方米）';
COMMENT ON COLUMN dmk.td_cluster_threshold.min_weighted_score IS '加权得分下限阈值';
COMMENT ON COLUMN dmk.td_cluster_threshold.max_region_distance IS '区域最大跨度距离阈值（米）';
COMMENT ON COLUMN dmk.td_cluster_threshold.rsrp_threshold IS 'RSRP 阈值（dBm）';
COMMENT ON COLUMN dmk.td_cluster_threshold.coverage_threshold IS '覆盖率阈值（0-1）';
COMMENT ON COLUMN dmk.td_cluster_threshold.threshold_config IS '其他阈值参数 JSON';
COMMENT ON COLUMN dmk.td_cluster_threshold.updated_by IS '最后更新人账号';
COMMENT ON COLUMN dmk.td_cluster_threshold.updated_time IS '记录更新时间';

CREATE TABLE IF NOT EXISTS dmk.td_custom_region (
    region_id varchar(64) PRIMARY KEY,
    account_id varchar(64) NOT NULL,
    provincecode integer,
    citycode integer,
    districtcode integer,
    region_wkt text NOT NULL,
    region_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        ST_Multi(ST_GeomFromText(region_wkt, 4326))::geometry(MultiPolygon, 4326)
    ) STORED,
    bbox numeric(10, 6)[],
    created_time timestamp without time zone NOT NULL DEFAULT now(),
    expire_time timestamp without time zone,
    is_deleted smallint NOT NULL DEFAULT 0
);

COMMENT ON TABLE dmk.td_custom_region IS '用户自定义区域表，region_wkt 固定 EPSG:4326。';
COMMENT ON COLUMN dmk.td_custom_region.region_id IS '自定义区域 ID';
COMMENT ON COLUMN dmk.td_custom_region.account_id IS '账号 ID（区域归属用户）';
COMMENT ON COLUMN dmk.td_custom_region.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_custom_region.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_custom_region.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_custom_region.region_wkt IS '自定义区域 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_custom_region.region_geom IS '由 region_wkt 生成的 MultiPolygon 几何列';
COMMENT ON COLUMN dmk.td_custom_region.bbox IS '区域外接矩形';
COMMENT ON COLUMN dmk.td_custom_region.created_time IS '区域创建时间';
COMMENT ON COLUMN dmk.td_custom_region.expire_time IS '过期时间（用于临时区域清理）';
COMMENT ON COLUMN dmk.td_custom_region.is_deleted IS '是否已删除：0=未删除 1=已删除';
CREATE INDEX IF NOT EXISTS idx_td_custom_region_query ON dmk.td_custom_region(account_id, provincecode, citycode, created_time DESC, is_deleted);
CREATE INDEX IF NOT EXISTS idx_td_custom_region_geom ON dmk.td_custom_region USING gist(region_geom);

-- =========================================================
-- 3. 区域栅格与地市报表指标表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.tm_grid_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    regionid varchar(64) NOT NULL,
    x_offset_20 varchar(32) NOT NULL,
    y_offset_20 varchar(32) NOT NULL,
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    grid_wkt text,
    grid_geom geometry(Polygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN grid_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(grid_wkt, 4326)::geometry(Polygon, 4326)
        END
    ) STORED,
    earfcn integer,
    rsrpcount bigint NOT NULL DEFAULT 0,
    totalrsrp numeric(20, 4),
    totalsinr numeric(20, 4),
    totalrsrq numeric(20, 4),
    avgrsrp numeric(10, 4),
    avgsinr numeric(10, 4),
    avgrsrq numeric(10, 4),
    rsrpgoodcount_105 bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_110 bigint NOT NULL DEFAULT 0,
    sinrgoodcount bigint NOT NULL DEFAULT 0,
    rsrqgoodcount bigint NOT NULL DEFAULT 0,
    rsrp_good_ratio_105 numeric(12, 6),
    rsrp_good_ratio_110 numeric(12, 6),
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    is_covered_105 smallint NOT NULL DEFAULT 0,
    is_covered_110 smallint NOT NULL DEFAULT 0,
    weakcover_mrcount bigint NOT NULL DEFAULT 0,
    overlap_mrcount bigint NOT NULL DEFAULT 0,
    overshoot_mrcount bigint NOT NULL DEFAULT 0,
    use_heat_5g numeric(12, 6),
    total_user_count bigint,
    user_count_4g bigint,
    user_count_5g bigint,
    user_market_share_4g numeric(12, 6),
    user_market_share_5g numeric(12, 6),
    operator_5g_reside_rate numeric(12, 6),
    top1_cellkey varchar(64),
    top1_cell_name varchar(128),
    top1_cell_lon numeric(10, 6),
    top1_cell_lat numeric(10, 6),
    top1_cell_rsrpcount bigint,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, regionid, x_offset_20, y_offset_20, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.tm_grid_coverage_m IS '栅格级覆盖指标月表，支撑 GIS 栅格图层、单栅格详情和底层聚合。';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.network_class IS '网络制式（4G/5G_SA/all）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.freq IS '频段，all 表示全部频段聚合';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.indoor_flag IS '室内外标识：0=室外 1=室内 -1=全部';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.regionid IS '栅格区域 ID';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.x_offset_20 IS '栅格 X 偏移';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.y_offset_20 IS '栅格 Y 偏移';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.center_lon IS '栅格中心点经度';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.center_lat IS '栅格中心点纬度';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.grid_wkt IS '栅格 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.grid_geom IS '由 grid_wkt 生成的 Polygon 几何列，用于 GiST 索引/WMS 发布';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.earfcn IS '主用频点号 EARFCN';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrpcount IS 'MR 采样点总数（RSRP 采样数）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.totalrsrp IS 'RSRP 累加值（用于聚合时再求平均）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.totalsinr IS 'SINR 累加值';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.totalrsrq IS 'RSRQ 累加值';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.avgsinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.avgrsrq IS '平均 RSRQ（dB）';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrpgoodcount_105 IS 'RSRP≥-105 的采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrpgoodcount_110 IS 'RSRP≥-110 的采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.sinrgoodcount IS 'SINR 达标采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrqgoodcount IS 'RSRQ 达标采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrp_good_ratio_105 IS 'RSRP≥-105 采样占比';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.rsrp_good_ratio_110 IS 'RSRP≥-110 采样占比';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)：rsrpgoodcount_105/rsrpcount';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)：rsrpgoodcount_110/rsrpcount';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.is_covered_105 IS '该栅格是否达 -105 覆盖：1=达标 0=未达标';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.is_covered_110 IS '该栅格是否达 -110 覆盖：1=达标 0=未达标';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.weakcover_mrcount IS '弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.overlap_mrcount IS '重叠覆盖采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.overshoot_mrcount IS '过覆盖采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.use_heat_5g IS '5G 使用热度';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.total_user_count IS '栅格内总用户数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.user_count_4g IS '4G 用户数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.user_count_5g IS '5G 用户数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.user_market_share_4g IS '4G 用户市场份额';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.user_market_share_5g IS '5G 用户市场份额';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.operator_5g_reside_rate IS '运营商 5G 驻留比';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.top1_cellkey IS 'TOP1 主服务小区 cellkey';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.top1_cell_name IS 'TOP1 主服务小区名称';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.top1_cell_lon IS 'TOP1 小区经度';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.top1_cell_lat IS 'TOP1 小区纬度';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.top1_cell_rsrpcount IS 'TOP1 小区在该栅格的采样数';
COMMENT ON COLUMN dmk.tm_grid_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_grid_cov_filter ON dmk.tm_grid_coverage_m(year_month, data_type, provincecode, citycode, districtcode, operator_name, network_class, freq, indoor_flag);
CREATE INDEX IF NOT EXISTS idx_tm_grid_cov_regionid ON dmk.tm_grid_coverage_m(year_month, regionid, x_offset_20, y_offset_20);
CREATE INDEX IF NOT EXISTS idx_tm_grid_cov_geom ON dmk.tm_grid_coverage_m USING gist(grid_geom) WHERE grid_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_region_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    region_level varchar(16) NOT NULL,
    region_code integer NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    rsrpcount bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_105 bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_110 bigint NOT NULL DEFAULT 0,
    grid_count bigint NOT NULL DEFAULT 0,
    mr_grid_count bigint NOT NULL DEFAULT 0,
    covered_grid_count_105 bigint NOT NULL DEFAULT 0,
    covered_grid_count_110 bigint NOT NULL DEFAULT 0,
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    grid_cover_rate_105 numeric(12, 6),
    grid_cover_rate_110 numeric(12, 6),
    avgrsrp numeric(10, 4),
    avgsinr numeric(10, 4),
    total_user_count bigint,
    user_count_4g bigint,
    user_count_5g bigint,
    user_ratio_4g numeric(12, 6),
    user_ratio_5g numeric(12, 6),
    total_user_market_share numeric(12, 6),
    user_market_share_4g numeric(12, 6),
    user_market_share_5g numeric(12, 6),
    operator_5g_reside_rate numeric(12, 6),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, region_level, region_code, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.tm_region_coverage_m IS '行政区域覆盖聚合月表，支撑区域概览、覆盖统计和 OTT 地市报表。';
COMMENT ON COLUMN dmk.tm_region_coverage_m.grid_count IS '区域内总栅格数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.mr_grid_count IS '区域内有 MR 采样点（rsrpcount>0）的栅格数，作为栅格覆盖率的分母';
COMMENT ON COLUMN dmk.tm_region_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_region_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_region_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_region_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_region_coverage_m.region_level IS '区域级别：nation/province/city/district';
COMMENT ON COLUMN dmk.tm_region_coverage_m.region_code IS '区域编码';
COMMENT ON COLUMN dmk.tm_region_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_region_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_region_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_region_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_region_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_region_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_region_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_region_coverage_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_region_coverage_m.freq IS '频段';
COMMENT ON COLUMN dmk.tm_region_coverage_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.tm_region_coverage_m.rsrpcount IS '区域内 MR 采样点总数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.rsrpgoodcount_105 IS 'RSRP≥-105 的采样数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.rsrpgoodcount_110 IS 'RSRP≥-110 的采样数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.covered_grid_count_105 IS '达到 -105 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.covered_grid_count_110 IS '达到 -110 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_region_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_region_coverage_m.grid_cover_rate_105 IS '栅格覆盖率(-105)：covered_grid_count_105/mr_grid_count';
COMMENT ON COLUMN dmk.tm_region_coverage_m.grid_cover_rate_110 IS '栅格覆盖率(-110)：covered_grid_count_110/mr_grid_count';
COMMENT ON COLUMN dmk.tm_region_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_region_coverage_m.avgsinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_region_coverage_m.total_user_count IS '区域内总用户数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_count_4g IS '4G 用户数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_count_5g IS '5G 用户数';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_ratio_4g IS '4G 用户占比';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_ratio_5g IS '5G 用户占比';
COMMENT ON COLUMN dmk.tm_region_coverage_m.total_user_market_share IS '总用户市场份额';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_market_share_4g IS '4G 用户市场份额';
COMMENT ON COLUMN dmk.tm_region_coverage_m.user_market_share_5g IS '5G 用户市场份额';
COMMENT ON COLUMN dmk.tm_region_coverage_m.operator_5g_reside_rate IS '运营商 5G 驻留比';
COMMENT ON COLUMN dmk.tm_region_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_region_cov_filter ON dmk.tm_region_coverage_m(year_month, data_type, provincecode, citycode, districtcode, operator_name, network_class);
CREATE INDEX IF NOT EXISTS idx_tm_region_cov_report ON dmk.tm_region_coverage_m(year_month, region_level, region_code, operator_name, network_class, grid_cover_rate_105 DESC);

-- =========================================================
-- 4. 楼宇与重点场景指标表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.tm_building_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    building_id varchar(64) NOT NULL,
    building_name varchar(128) NOT NULL,
    building_type varchar(64),
    building_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    building_area numeric(18, 4),
    population_density numeric(14, 4),
    aoi_wkt text,
    aoi_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN aoi_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(aoi_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    rsrpcount bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_105 bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_110 bigint NOT NULL DEFAULT 0,
    grid_count bigint NOT NULL DEFAULT 0,
    mr_grid_count bigint NOT NULL DEFAULT 0,
    covered_grid_count_105 bigint NOT NULL DEFAULT 0,
    covered_grid_count_110 bigint NOT NULL DEFAULT 0,
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    grid_cover_rate_105 numeric(12, 6),
    grid_cover_rate_110 numeric(12, 6),
    building_cover_rate numeric(12, 6),
    wireless_cover_rate numeric(12, 6),
    indoor_cover_rate numeric(12, 6),
    avgrsrp numeric(10, 4),
    avgsinr numeric(10, 4),
    avgrsrq numeric(10, 4),
    weakcover_mrcount bigint NOT NULL DEFAULT 0,
    overlap_mrcount bigint NOT NULL DEFAULT 0,
    overlap_total_value numeric(20, 4),
    overlap_rate numeric(12, 6),
    overlap_avgrsrp numeric(10, 4),
    overshoot_mrcount bigint NOT NULL DEFAULT 0,
    overshoot_total_value numeric(20, 4),
    overshoot_rate numeric(12, 6),
    overshoot_avgrsrp numeric(10, 4),
    mod_interference_mrcount bigint NOT NULL DEFAULT 0,
    mod_interference_total_value numeric(20, 4),
    mod_interference_avgrsrp numeric(10, 4),
    mod_interference_ratio numeric(12, 6),
    use_heat_5g numeric(12, 6),
    total_user_count bigint,
    user_count_4g bigint,
    user_count_5g bigint,
    user_market_share_4g numeric(12, 6),
    user_market_share_5g numeric(12, 6),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, building_id, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.tm_building_coverage_m IS '楼宇覆盖指标月表，回填楼宇维度字段，支撑楼宇图层、对比和报表。';
COMMENT ON COLUMN dmk.tm_building_coverage_m.grid_count IS '楼宇覆盖范围内总栅格数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mr_grid_count IS '楼宇覆盖范围内有 MR 采样点的栅格数，作为楼宇栅格覆盖率的分母';
COMMENT ON COLUMN dmk.tm_building_coverage_m.total_user_count IS '楼宇内总用户数，OTT 楼宇报表 total_user_count 字段直读';
COMMENT ON COLUMN dmk.tm_building_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_building_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_building_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_building_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_id IS '楼宇 ID';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_name IS '楼宇名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_type IS '楼宇类型编码';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_type_name IS '楼宇类型名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_building_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_building_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_building_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.center_lon IS '楼宇中心点经度';
COMMENT ON COLUMN dmk.tm_building_coverage_m.center_lat IS '楼宇中心点纬度';
COMMENT ON COLUMN dmk.tm_building_coverage_m.bbox IS '楼宇外接矩形';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_area IS '楼宇面积（平方米，冗余自楼宇基础维表）';
COMMENT ON COLUMN dmk.tm_building_coverage_m.population_density IS '人口密度';
COMMENT ON COLUMN dmk.tm_building_coverage_m.aoi_wkt IS '楼宇 AOI WKT，EPSG:4326';
COMMENT ON COLUMN dmk.tm_building_coverage_m.aoi_geom IS '由 aoi_wkt 生成的 MultiPolygon 几何列';
COMMENT ON COLUMN dmk.tm_building_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_building_coverage_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_building_coverage_m.freq IS '频段';
COMMENT ON COLUMN dmk.tm_building_coverage_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.tm_building_coverage_m.rsrpcount IS '楼宇覆盖范围内 MR 采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.rsrpgoodcount_105 IS 'RSRP≥-105 的采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.rsrpgoodcount_110 IS 'RSRP≥-110 的采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.covered_grid_count_105 IS '达到 -105 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.covered_grid_count_110 IS '达到 -110 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_building_coverage_m.grid_cover_rate_105 IS '栅格覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_building_coverage_m.grid_cover_rate_110 IS '栅格覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_building_coverage_m.building_cover_rate IS '楼宇覆盖率';
COMMENT ON COLUMN dmk.tm_building_coverage_m.wireless_cover_rate IS '无线覆盖率';
COMMENT ON COLUMN dmk.tm_building_coverage_m.indoor_cover_rate IS '室内覆盖率';
COMMENT ON COLUMN dmk.tm_building_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_building_coverage_m.avgsinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_building_coverage_m.avgrsrq IS '平均 RSRQ（dB），支撑楼宇竞对无线网络覆盖';
COMMENT ON COLUMN dmk.tm_building_coverage_m.weakcover_mrcount IS '弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overlap_mrcount IS '重叠覆盖采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overlap_total_value IS '重叠覆盖总值';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overlap_rate IS '重叠覆盖率';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overlap_avgrsrp IS '重叠覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overshoot_mrcount IS '过覆盖采样数';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overshoot_total_value IS '过覆盖总值';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overshoot_rate IS '过覆盖率';
COMMENT ON COLUMN dmk.tm_building_coverage_m.overshoot_avgrsrp IS '过覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mod_interference_mrcount IS 'MOD 干扰采样数，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mod_interference_total_value IS 'MOD 干扰采样点总值，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mod_interference_avgrsrp IS 'MOD 干扰平均 RSRP（dBm），4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_building_coverage_m.mod_interference_ratio IS 'MOD 干扰占比，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_building_coverage_m.use_heat_5g IS '5G 使用热度';
COMMENT ON COLUMN dmk.tm_building_coverage_m.user_count_4g IS '楼宇 4G 用户数，用于替换原无线市场份额指标';
COMMENT ON COLUMN dmk.tm_building_coverage_m.user_count_5g IS '楼宇 5G 用户数，用于替换原无线市场份额指标';
COMMENT ON COLUMN dmk.tm_building_coverage_m.user_market_share_4g IS '楼宇 4G 用户市场份额';
COMMENT ON COLUMN dmk.tm_building_coverage_m.user_market_share_5g IS '楼宇 5G 用户市场份额';
COMMENT ON COLUMN dmk.tm_building_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_building_cov_filter ON dmk.tm_building_coverage_m(year_month, data_type, provincecode, citycode, districtcode, building_type, operator_name, network_class);
CREATE INDEX IF NOT EXISTS idx_tm_building_cov_report ON dmk.tm_building_coverage_m(year_month, citycode, building_type, grid_cover_rate_110 DESC);
CREATE INDEX IF NOT EXISTS idx_tm_building_cov_geom ON dmk.tm_building_coverage_m USING gist(aoi_geom) WHERE aoi_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_building_user_wifi_m (
    year_month varchar(7) NOT NULL,
    building_id varchar(64) NOT NULL,
    building_name varchar(128),
    building_type varchar(64),
    provincecode integer,
    citycode integer,
    districtcode integer,
    operator_name varchar(32) NOT NULL,
    wifi_total_user_count bigint,
    wifi_user_count bigint,
    wifi_market_share numeric(12, 6),
    wifi_signal_strength numeric(10, 4),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, building_id, operator_name)
);

COMMENT ON TABLE dmk.tm_building_user_wifi_m IS '楼宇 WiFi 指标月表，楼宇 4G/5G_SA 用户数和市场份额落在 tm_building_coverage_m。';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.building_id IS '楼宇 ID';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.building_name IS '楼宇名称';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.building_type IS '楼宇类型编码';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.wifi_total_user_count IS '楼宇内 WiFi 总用户数';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.wifi_user_count IS '本运营商 WiFi 用户数';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.wifi_market_share IS '本运营商 WiFi 市场份额';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.wifi_signal_strength IS 'WiFi 平均信号强度（dBm）';
COMMENT ON COLUMN dmk.tm_building_user_wifi_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_building_user_wifi_query ON dmk.tm_building_user_wifi_m(year_month, provincecode, citycode, districtcode, building_type);

CREATE TABLE IF NOT EXISTS dmk.tm_scene_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    scene_id varchar(64) NOT NULL,
    scene_name varchar(128) NOT NULL,
    scene_type varchar(64) NOT NULL,
    scene_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    aoi_wkt text,
    aoi_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN aoi_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(aoi_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    rsrpcount bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_105 bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_110 bigint NOT NULL DEFAULT 0,
    grid_count bigint NOT NULL DEFAULT 0,
    mr_grid_count bigint NOT NULL DEFAULT 0,
    covered_grid_count_105 bigint NOT NULL DEFAULT 0,
    covered_grid_count_110 bigint NOT NULL DEFAULT 0,
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    grid_cover_rate_105 numeric(12, 6),
    grid_cover_rate_110 numeric(12, 6),
    avgrsrp numeric(10, 4),
    avgsinr numeric(10, 4),
    weakcover_mrcount bigint NOT NULL DEFAULT 0,
    overlap_mrcount bigint NOT NULL DEFAULT 0,
    overlap_total_value numeric(20, 4),
    overlap_rate numeric(12, 6),
    overlap_avgrsrp numeric(10, 4),
    overshoot_mrcount bigint NOT NULL DEFAULT 0,
    overshoot_total_value numeric(20, 4),
    overshoot_rate numeric(12, 6),
    overshoot_avgrsrp numeric(10, 4),
    mod_interference_mrcount bigint NOT NULL DEFAULT 0,
    mod_interference_total_value numeric(20, 4),
    mod_interference_avgrsrp numeric(10, 4),
    mod_interference_ratio numeric(12, 6),
    total_user_count bigint,
    user_count_4g bigint,
    user_count_5g bigint,
    user_ratio_4g numeric(12, 6),
    user_ratio_5g numeric(12, 6),
    total_user_market_share numeric(12, 6),
    user_market_share_4g numeric(12, 6),
    user_market_share_5g numeric(12, 6),
    operator_5g_reside_rate numeric(12, 6),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, scene_id, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.tm_scene_coverage_m IS '重点场景覆盖指标月表，回填场景维度字段，支撑场景概览、用户分析、对比和报表。';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.grid_count IS '场景覆盖范围内总栅格数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mr_grid_count IS '场景覆盖范围内有 MR 采样点的栅格数，作为场景栅格覆盖率的分母';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.scene_id IS '场景 ID';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.scene_name IS '场景名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.scene_type IS '场景类型编码';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.scene_type_name IS '场景类型名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.center_lon IS '场景中心点经度';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.center_lat IS '场景中心点纬度';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.bbox IS '场景外接矩形';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.aoi_wkt IS '场景 AOI WKT';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.aoi_geom IS '由 aoi_wkt 生成的 MultiPolygon 几何列';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.freq IS '频段';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.rsrpcount IS '场景覆盖范围内 MR 采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.rsrpgoodcount_105 IS 'RSRP≥-105 的采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.rsrpgoodcount_110 IS 'RSRP≥-110 的采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.covered_grid_count_105 IS '达到 -105 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.covered_grid_count_110 IS '达到 -110 覆盖阈值的栅格数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.grid_cover_rate_105 IS '栅格覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.grid_cover_rate_110 IS '栅格覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.avgsinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.weakcover_mrcount IS '弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overlap_mrcount IS '重叠覆盖采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overlap_total_value IS '重叠覆盖总值';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overlap_rate IS '重叠覆盖率';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overlap_avgrsrp IS '重叠覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overshoot_mrcount IS '过覆盖采样数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overshoot_total_value IS '过覆盖总值';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overshoot_rate IS '过覆盖率';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.overshoot_avgrsrp IS '过覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mod_interference_mrcount IS 'MOD 干扰采样数，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mod_interference_total_value IS 'MOD 干扰采样点总值，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mod_interference_avgrsrp IS 'MOD 干扰平均 RSRP（dBm），4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.mod_interference_ratio IS 'MOD 干扰占比，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.total_user_count IS '场景内总用户数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_count_4g IS '4G 用户数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_count_5g IS '5G 用户数';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_ratio_4g IS '4G 用户占比';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_ratio_5g IS '5G 用户占比';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.total_user_market_share IS '总用户市场份额';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_market_share_4g IS '4G 用户市场份额';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.user_market_share_5g IS '5G 用户市场份额';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.operator_5g_reside_rate IS '运营商 5G 驻留比';
COMMENT ON COLUMN dmk.tm_scene_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_scene_cov_filter ON dmk.tm_scene_coverage_m(year_month, data_type, provincecode, citycode, scene_type, scene_name, operator_name, network_class);
CREATE INDEX IF NOT EXISTS idx_tm_scene_cov_geom ON dmk.tm_scene_coverage_m USING gist(aoi_geom) WHERE aoi_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_scene_grid_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    scene_id varchar(64) NOT NULL,
    scene_name varchar(128) NOT NULL,
    scene_type varchar(64) NOT NULL,
    scene_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    regionid varchar(64) NOT NULL,
    x_offset_20 varchar(32) NOT NULL,
    y_offset_20 varchar(32) NOT NULL,
    grid_wkt text,
    grid_geom geometry(Polygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN grid_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(grid_wkt, 4326)::geometry(Polygon, 4326)
        END
    ) STORED,
    rsrpcount bigint NOT NULL DEFAULT 0,
    avgrsrp numeric(10, 4),
    avgsinr numeric(10, 4),
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    grid_cover_rate_105 numeric(12, 6),
    grid_cover_rate_110 numeric(12, 6),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, scene_id, regionid, x_offset_20, y_offset_20, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.tm_scene_grid_coverage_m IS '重点场景栅格桥接月表，支撑 /api/scenes/layer 与场景栅格发布。';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.scene_id IS '场景 ID';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.scene_name IS '场景名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.scene_type IS '场景类型编码';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.scene_type_name IS '场景类型名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.freq IS '频段';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.regionid IS '栅格区域 ID';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.x_offset_20 IS '栅格 X 偏移';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.y_offset_20 IS '栅格 Y 偏移';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.grid_wkt IS '栅格 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.grid_geom IS '由 grid_wkt 生成的 Polygon 几何列';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.rsrpcount IS '栅格 MR 采样数';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.avgsinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.grid_cover_rate_105 IS '栅格覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.grid_cover_rate_110 IS '栅格覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_scene_grid_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_scene_grid_cov_filter ON dmk.tm_scene_grid_coverage_m(year_month, data_type, provincecode, citycode, scene_type, scene_name, operator_name, network_class);
CREATE INDEX IF NOT EXISTS idx_tm_scene_grid_cov_geom ON dmk.tm_scene_grid_coverage_m USING gist(grid_geom) WHERE grid_geom IS NOT NULL;

-- =========================================================
-- 5. 工参与小区覆盖指标表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.td_building_grid_m (
    year_month varchar(7) NOT NULL,
    data_type integer NOT NULL,
    building_id varchar(64) NOT NULL,
    regionid varchar(64) NOT NULL,
    x_offset_20 varchar(32) NOT NULL,
    y_offset_20 varchar(32) NOT NULL,
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    building_name varchar(128),
    provincecode integer,
    citycode integer,
    districtcode integer,
    grid_wkt text,
    grid_geom geometry(Polygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN grid_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(grid_wkt, 4326)::geometry(Polygon, 4326)
        END
    ) STORED,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, building_id, regionid, x_offset_20, y_offset_20, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.td_building_grid_m IS '楼宇栅格桥接月表，支撑 /api/buildings/layer 和楼宇-栅格关联。';
COMMENT ON COLUMN dmk.td_building_grid_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.td_building_grid_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.td_building_grid_m.building_id IS '楼宇 ID';
COMMENT ON COLUMN dmk.td_building_grid_m.regionid IS '栅格区域 ID';
COMMENT ON COLUMN dmk.td_building_grid_m.x_offset_20 IS '栅格 X 偏移';
COMMENT ON COLUMN dmk.td_building_grid_m.y_offset_20 IS '栅格 Y 偏移';
COMMENT ON COLUMN dmk.td_building_grid_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.td_building_grid_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.td_building_grid_m.freq IS '频段';
COMMENT ON COLUMN dmk.td_building_grid_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.td_building_grid_m.building_name IS '楼宇名称（冗余）';
COMMENT ON COLUMN dmk.td_building_grid_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_building_grid_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_building_grid_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_building_grid_m.grid_wkt IS '栅格 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.td_building_grid_m.grid_geom IS '由 grid_wkt 生成的 Polygon 几何列';
COMMENT ON COLUMN dmk.td_building_grid_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_building_grid_query ON dmk.td_building_grid_m(year_month, data_type, provincecode, citycode, districtcode, operator_name, network_class, freq, indoor_flag);
CREATE INDEX IF NOT EXISTS idx_td_building_grid_geom ON dmk.td_building_grid_m USING gist(grid_geom) WHERE grid_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.td_building_cell_m (
    year_month varchar(7) NOT NULL,
    data_type integer NOT NULL,
    building_id varchar(64) NOT NULL,
    cellkey varchar(64) NOT NULL,
    cell_name varchar(128),
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    freq varchar(32) NOT NULL DEFAULT 'all',
    indoor_flag smallint NOT NULL DEFAULT -1,
    provincecode integer,
    citycode integer,
    districtcode integer,
    cell_lon numeric(10, 6),
    cell_lat numeric(10, 6),
    cell_wkt text,
    cell_geom geometry(Point, 4326) GENERATED ALWAYS AS (
        CASE WHEN cell_wkt IS NOT NULL THEN ST_GeomFromText(cell_wkt, 4326)::geometry(Point, 4326)
             WHEN cell_lon IS NOT NULL AND cell_lat IS NOT NULL THEN ST_SetSRID(ST_MakePoint(cell_lon, cell_lat), 4326)::geometry(Point, 4326)
             ELSE NULL
        END
    ) STORED,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, building_id, cellkey, operator_name, network_class, freq, indoor_flag)
);

COMMENT ON TABLE dmk.td_building_cell_m IS '楼宇小区桥接月表，支撑 /api/buildings/cells 与楼宇-小区关联。';
COMMENT ON COLUMN dmk.td_building_cell_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.td_building_cell_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.td_building_cell_m.building_id IS '楼宇 ID';
COMMENT ON COLUMN dmk.td_building_cell_m.cellkey IS '小区唯一键 cellkey';
COMMENT ON COLUMN dmk.td_building_cell_m.cell_name IS '小区名称（冗余）';
COMMENT ON COLUMN dmk.td_building_cell_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.td_building_cell_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.td_building_cell_m.freq IS '频段';
COMMENT ON COLUMN dmk.td_building_cell_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.td_building_cell_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.td_building_cell_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.td_building_cell_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.td_building_cell_m.cell_lon IS '小区经度';
COMMENT ON COLUMN dmk.td_building_cell_m.cell_lat IS '小区纬度';
COMMENT ON COLUMN dmk.td_building_cell_m.cell_wkt IS '小区点 WKT';
COMMENT ON COLUMN dmk.td_building_cell_m.cell_geom IS '小区点几何列（由 cell_wkt 或经纬度生成）';
COMMENT ON COLUMN dmk.td_building_cell_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_td_building_cell_query ON dmk.td_building_cell_m(year_month, data_type, provincecode, citycode, districtcode, operator_name, network_class, freq, indoor_flag);
CREATE INDEX IF NOT EXISTS idx_td_building_cell_geom ON dmk.td_building_cell_m USING gist(cell_geom) WHERE cell_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_cell_grid_coverage_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    operator_name varchar(32) NOT NULL,
    network_class varchar(32) NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    cellkey varchar(64) NOT NULL,
    cell_name varchar(128),
    cell_lon numeric(10, 6),
    cell_lat numeric(10, 6),
    cell_wkt text,
    cell_geom geometry(Point, 4326) GENERATED ALWAYS AS (
        CASE WHEN cell_wkt IS NOT NULL THEN ST_GeomFromText(cell_wkt, 4326)::geometry(Point, 4326)
             WHEN cell_lon IS NOT NULL AND cell_lat IS NOT NULL THEN ST_SetSRID(ST_MakePoint(cell_lon, cell_lat), 4326)::geometry(Point, 4326)
             ELSE NULL
        END
    ) STORED,
    pci varchar(32),
    indoor_flag smallint NOT NULL DEFAULT -1,
    azimuth integer,
    freq varchar(32) NOT NULL DEFAULT 'all',
    vendor varchar(64),
    antenna_height numeric(10, 2),
    mechanical_downdip numeric(10, 2),
    electron_downdip numeric(10, 2),
    cover_type varchar(64),
    rspower numeric(12, 4),
    regionid varchar(64) NOT NULL,
    x_offset_20 varchar(32) NOT NULL,
    y_offset_20 varchar(32) NOT NULL,
    grid_lon numeric(10, 6),
    grid_lat numeric(10, 6),
    grid_wkt text,
    grid_geom geometry(Polygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN grid_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(grid_wkt, 4326)::geometry(Polygon, 4326)
        END
    ) STORED,
    cell_grid_line_wkt text,
    cell_grid_line_geom geometry(LineString, 4326) GENERATED ALWAYS AS (
        CASE WHEN cell_grid_line_wkt IS NULL THEN NULL
             ELSE ST_GeomFromText(cell_grid_line_wkt, 4326)::geometry(LineString, 4326)
        END
    ) STORED,
    rsrpcount bigint NOT NULL DEFAULT 0,
    totalrsrp numeric(20, 4),
    totalsinr numeric(20, 4),
    avgrsrp numeric(10, 4),
    avg_sinr numeric(10, 4),
    rsrpgoodcount_105 bigint NOT NULL DEFAULT 0,
    rsrpgoodcount_110 bigint NOT NULL DEFAULT 0,
    mr_cover_rate_105 numeric(12, 6),
    mr_cover_rate_110 numeric(12, 6),
    weakcover_mrcount bigint NOT NULL DEFAULT 0,
    overlap_mrcount bigint NOT NULL DEFAULT 0,
    overlap_total_value numeric(20, 4),
    overlap_rate numeric(12, 6),
    overlap_avgrsrp numeric(10, 4),
    overshoot_mrcount bigint NOT NULL DEFAULT 0,
    overshoot_total_value numeric(20, 4),
    overshoot_rate numeric(12, 6),
    overshoot_avgrsrp numeric(10, 4),
    mod_interference_mrcount bigint NOT NULL DEFAULT 0,
    mod_interference_total_value numeric(20, 4),
    mod_interference_avgrsrp numeric(10, 4),
    mod_interference_ratio numeric(12, 6),
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, data_type, operator_name, network_class, freq, indoor_flag, cellkey, regionid, x_offset_20, y_offset_20)
);

COMMENT ON TABLE dmk.tm_cell_grid_coverage_m IS '小区覆盖栅格月表，支撑单小区覆盖栅格、栅格 TOP 小区和栅格-小区连线。';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cellkey IS '小区唯一键 cellkey';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_name IS '小区名称';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_lon IS '小区经度';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_lat IS '小区纬度';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_wkt IS '小区点 WKT';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_geom IS '小区点几何列';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.pci IS '物理小区标识 PCI';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.indoor_flag IS '室内外标识';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.azimuth IS '天线方位角（度）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.freq IS '频段';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.vendor IS '设备厂家';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.antenna_height IS '天线挂高（米）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mechanical_downdip IS '机械下倾角';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.electron_downdip IS '电子下倾角';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cover_type IS '覆盖类型';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.rspower IS '参考信号发射功率';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.regionid IS '覆盖栅格区域 ID';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.x_offset_20 IS '覆盖栅格 X 偏移';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.y_offset_20 IS '覆盖栅格 Y 偏移';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.grid_lon IS '覆盖栅格中心点经度';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.grid_lat IS '覆盖栅格中心点纬度';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.grid_wkt IS '覆盖栅格 WKT';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.grid_geom IS '覆盖栅格 Polygon 几何列';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_grid_line_wkt IS '小区→栅格连线 WKT（LineString）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.cell_grid_line_geom IS '小区→栅格连线 LineString 几何列';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.rsrpcount IS '该小区在该栅格的 MR 采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.totalrsrp IS 'RSRP 累加值';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.totalsinr IS 'SINR 累加值';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.avgrsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.avg_sinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.rsrpgoodcount_105 IS 'RSRP≥-105 的采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.rsrpgoodcount_110 IS 'RSRP≥-110 的采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mr_cover_rate_105 IS 'MR 覆盖率(-105)';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mr_cover_rate_110 IS 'MR 覆盖率(-110)';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.weakcover_mrcount IS '弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overlap_mrcount IS '重叠覆盖采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overlap_total_value IS '重叠覆盖总值';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overlap_rate IS '重叠覆盖率';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overlap_avgrsrp IS '重叠覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overshoot_mrcount IS '过覆盖采样数';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overshoot_total_value IS '过覆盖总值';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overshoot_rate IS '过覆盖率';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.overshoot_avgrsrp IS '过覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mod_interference_mrcount IS 'MOD 干扰采样数，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mod_interference_total_value IS 'MOD 干扰采样点总值，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mod_interference_avgrsrp IS 'MOD 干扰平均 RSRP（dBm），4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.mod_interference_ratio IS 'MOD 干扰占比，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cell_grid_coverage_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_cell_grid_cell ON dmk.tm_cell_grid_coverage_m(year_month, cellkey, network_class, rsrpcount DESC);
CREATE INDEX IF NOT EXISTS idx_tm_cell_grid_grid ON dmk.tm_cell_grid_coverage_m(year_month, regionid, x_offset_20, y_offset_20, operator_name, network_class, rsrpcount DESC);
CREATE INDEX IF NOT EXISTS idx_tm_cell_grid_cell_geom ON dmk.tm_cell_grid_coverage_m USING gist(cell_geom) WHERE cell_geom IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_tm_cell_grid_grid_geom ON dmk.tm_cell_grid_coverage_m USING gist(grid_geom) WHERE grid_geom IS NOT NULL;

-- =========================================================
-- 6. 聚类、加权得分与质差指标表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.tm_cluster_area_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    data_type integer NOT NULL,
    cluster_id varchar(64) NOT NULL,
    cluster_name varchar(128),
    cluster_type varchar(64) NOT NULL,
    top_type varchar(32) NOT NULL DEFAULT 'all',
    network_class varchar(32) NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    area_wkt text,
    area_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN area_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(area_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    grid_count bigint NOT NULL DEFAULT 0,
    covered_grid_count bigint NOT NULL DEFAULT 0,
    weak_grid_count bigint NOT NULL DEFAULT 0,
    weak_grid_ratio numeric(12, 6),
    area_size numeric(18, 4),
    perimeter numeric(18, 4),
    weighted_score numeric(12, 6),
    business_score numeric(12, 6),
    area_score numeric(12, 6),
    value_score numeric(12, 6),
    coverage_score numeric(12, 6),
    user_score numeric(12, 6),
    rsrpcount bigint NOT NULL DEFAULT 0,
    weakcover_mrcount bigint NOT NULL DEFAULT 0,
    overlap_mrcount bigint NOT NULL DEFAULT 0,
    overlap_total_value numeric(20, 4),
    overlap_rate numeric(12, 6),
    overlap_avgrsrp numeric(10, 4),
    overshoot_mrcount bigint NOT NULL DEFAULT 0,
    overshoot_total_value numeric(20, 4),
    overshoot_rate numeric(12, 6),
    overshoot_avgrsrp numeric(10, 4),
    mod_interference_mrcount bigint NOT NULL DEFAULT 0,
    mod_interference_total_value numeric(20, 4),
    mod_interference_avgrsrp numeric(10, 4),
    mod_interference_ratio numeric(12, 6),
    avg_rsrp numeric(10, 4),
    avg_sinr numeric(10, 4),
    mr_cover_rate numeric(12, 6),
    total_user_count bigint,
    user_count_4g bigint,
    user_count_5g bigint,
    user_density numeric(18, 4),
    high_value_user_ratio numeric(12, 6),
    avg_arpu numeric(12, 4),
    vip_user_count bigint,
    total_traffic_gb numeric(18, 4),
    voice_minutes numeric(18, 4),
    video_user_ratio numeric(12, 6),
    related_scene_count integer,
    is_feedback smallint NOT NULL DEFAULT 0,
    rank_no integer,
    percent_rank numeric(12, 6),
    feedback_source varchar(32),
    update_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, cluster_id)
);

COMMENT ON TABLE dmk.tm_cluster_area_m IS '聚类区域月表，支撑聚类清单、加权得分、多维分析和聚类区域 WMS。';
COMMENT ON COLUMN dmk.tm_cluster_area_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_cluster_area_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_cluster_area_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_cluster_area_m.data_type IS '数据来源类型';
COMMENT ON COLUMN dmk.tm_cluster_area_m.cluster_id IS '聚类区域 ID';
COMMENT ON COLUMN dmk.tm_cluster_area_m.cluster_name IS '聚类区域名称';
COMMENT ON COLUMN dmk.tm_cluster_area_m.cluster_type IS '聚类类型（弱覆盖/超忙/综合质差等）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.top_type IS 'TOP 档位标识：top10/top50/all 等';
COMMENT ON COLUMN dmk.tm_cluster_area_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_cluster_area_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_cluster_area_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_cluster_area_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_cluster_area_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_cluster_area_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_cluster_area_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_cluster_area_m.center_lon IS '聚类区域中心点经度';
COMMENT ON COLUMN dmk.tm_cluster_area_m.center_lat IS '聚类区域中心点纬度';
COMMENT ON COLUMN dmk.tm_cluster_area_m.bbox IS '聚类区域外接矩形';
COMMENT ON COLUMN dmk.tm_cluster_area_m.area_wkt IS '聚类区域 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.tm_cluster_area_m.area_geom IS '聚类区域 MultiPolygon 几何列';
COMMENT ON COLUMN dmk.tm_cluster_area_m.grid_count IS '聚类区域内栅格总数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.covered_grid_count IS '聚类区域内已覆盖栅格数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.weak_grid_count IS '聚类区域内弱覆盖栅格数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.weak_grid_ratio IS '弱覆盖栅格占比';
COMMENT ON COLUMN dmk.tm_cluster_area_m.area_size IS '聚类区域面积（平方米）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.perimeter IS '聚类区域周长（米）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.weighted_score IS '聚类加权综合得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.business_score IS '业务维度得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.area_score IS '区域维度得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.value_score IS '价值维度得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.coverage_score IS '覆盖维度得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.user_score IS '用户维度得分';
COMMENT ON COLUMN dmk.tm_cluster_area_m.rsrpcount IS '聚类区域内 RSRP 采样点数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.weakcover_mrcount IS '聚类区域内弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overlap_mrcount IS '聚类区域内重叠覆盖采样数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overlap_total_value IS '聚类区域内重叠覆盖总值';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overlap_rate IS '聚类区域内重叠覆盖率';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overlap_avgrsrp IS '聚类区域内重叠覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overshoot_mrcount IS '聚类区域内过覆盖采样数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overshoot_total_value IS '聚类区域内过覆盖总值';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overshoot_rate IS '聚类区域内过覆盖率';
COMMENT ON COLUMN dmk.tm_cluster_area_m.overshoot_avgrsrp IS '聚类区域内过覆盖平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.mod_interference_mrcount IS '聚类区域内 MOD 干扰采样数，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cluster_area_m.mod_interference_total_value IS '聚类区域内 MOD 干扰采样点总值，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cluster_area_m.mod_interference_avgrsrp IS '聚类区域内 MOD 干扰平均 RSRP（dBm），4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cluster_area_m.mod_interference_ratio IS '聚类区域内 MOD 干扰占比，4G 按 MOD3、5G 按 MOD30 解释';
COMMENT ON COLUMN dmk.tm_cluster_area_m.avg_rsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.avg_sinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.mr_cover_rate IS 'MR 覆盖率';
COMMENT ON COLUMN dmk.tm_cluster_area_m.total_user_count IS '区域内总用户数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.user_count_4g IS '4G 用户数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.user_count_5g IS '5G 用户数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.user_density IS '用户密度（人/平方公里）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.high_value_user_ratio IS '高价值用户占比';
COMMENT ON COLUMN dmk.tm_cluster_area_m.avg_arpu IS '平均 ARPU';
COMMENT ON COLUMN dmk.tm_cluster_area_m.vip_user_count IS 'VIP 用户数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.total_traffic_gb IS '总流量（GB）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.voice_minutes IS '语音业务时长（分钟）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.video_user_ratio IS '视频用户占比';
COMMENT ON COLUMN dmk.tm_cluster_area_m.related_scene_count IS '关联场景数';
COMMENT ON COLUMN dmk.tm_cluster_area_m.is_feedback IS '是否已反馈：0=未反馈 1=已反馈';
COMMENT ON COLUMN dmk.tm_cluster_area_m.rank_no IS '加权得分排名（同档位内）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.percent_rank IS '排名分位数（0-1）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.feedback_source IS '反馈来源（manual/system/external 等）';
COMMENT ON COLUMN dmk.tm_cluster_area_m.update_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_cluster_area_filter ON dmk.tm_cluster_area_m(year_month, data_type, provincecode, citycode, network_class, cluster_type, top_type, weighted_score DESC);
CREATE INDEX IF NOT EXISTS idx_tm_cluster_area_geom ON dmk.tm_cluster_area_m USING gist(area_geom) WHERE area_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_cluster_feedback (
    feedback_id varchar(64) PRIMARY KEY,
    year_month varchar(7) NOT NULL,
    cluster_id varchar(64) NOT NULL,
    problem_reason_type varchar(64) NOT NULL,
    solution_type varchar(64) NOT NULL,
    problem_desc text NOT NULL,
    solution_desc text NOT NULL,
    feedback_user varchar(64) NOT NULL,
    feedback_source varchar(32) NOT NULL DEFAULT 'manual',
    is_feedback smallint NOT NULL DEFAULT 1,
    updated_by varchar(64),
    update_time timestamp without time zone NOT NULL DEFAULT now()
);

COMMENT ON TABLE dmk.tm_cluster_feedback IS '质差聚类区域反馈表，保存问题根因、解决措施和反馈来源。';
COMMENT ON COLUMN dmk.tm_cluster_feedback.feedback_id IS '反馈记录 ID';
COMMENT ON COLUMN dmk.tm_cluster_feedback.year_month IS '所属账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_cluster_feedback.cluster_id IS '聚类区域 ID';
COMMENT ON COLUMN dmk.tm_cluster_feedback.problem_reason_type IS '问题根因类型编码';
COMMENT ON COLUMN dmk.tm_cluster_feedback.solution_type IS '解决措施类型编码';
COMMENT ON COLUMN dmk.tm_cluster_feedback.problem_desc IS '问题描述（自由文本）';
COMMENT ON COLUMN dmk.tm_cluster_feedback.solution_desc IS '解决措施描述（自由文本）';
COMMENT ON COLUMN dmk.tm_cluster_feedback.feedback_user IS '反馈提交用户账号';
COMMENT ON COLUMN dmk.tm_cluster_feedback.feedback_source IS '反馈来源：manual/system/external 等';
COMMENT ON COLUMN dmk.tm_cluster_feedback.is_feedback IS '是否有效反馈：1=是 0=已撤销';
COMMENT ON COLUMN dmk.tm_cluster_feedback.updated_by IS '最后更新人账号';
COMMENT ON COLUMN dmk.tm_cluster_feedback.update_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_cluster_feedback_query ON dmk.tm_cluster_feedback(year_month, cluster_id, update_time DESC);

CREATE TABLE IF NOT EXISTS dmk.tm_poor_region_metric_m (
    year_month varchar(7) NOT NULL,
    year integer NOT NULL,
    month integer NOT NULL,
    region_level varchar(16) NOT NULL,
    region_code integer NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    stat_type varchar(32) NOT NULL,
    poor_type varchar(32) NOT NULL,
    metric_group varchar(64) NOT NULL,
    scene_type varchar(64) NOT NULL DEFAULT 'all',
    scene_type_name varchar(128),
    network_class varchar(32) NOT NULL DEFAULT 'all',
    metric_code varchar(64) NOT NULL,
    metric_name varchar(128),
    metric_value numeric(20, 6),
    rank_no integer,
    scene_count bigint,
    cell_count bigint,
    traffic_count numeric(20, 6),
    poor_scene_count bigint,
    poor_cell_count bigint,
    busy_cell_count bigint,
    region_wkt text,
    region_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN region_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(region_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, region_level, region_code, stat_type, poor_type, metric_group, scene_type, network_class, metric_code)
);

COMMENT ON TABLE dmk.tm_poor_region_metric_m IS '质差区域指标月表，统一支撑概览、走势、GIS 地图和指标排名。';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.year IS '账期年份';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.month IS '账期月份';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.region_level IS '区域级别';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.region_code IS '区域编码';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.stat_type IS '统计页签类型（概览/趋势/地图/排名）';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.poor_type IS '质差类型（弱覆盖/超忙/感知差等）';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.metric_group IS '指标分组';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.scene_type IS '场景类型，all 表示全部场景';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.scene_type_name IS '场景类型名称';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.metric_code IS '指标编码';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.metric_name IS '指标名称';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.metric_value IS '指标值';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.rank_no IS '排名序号';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.scene_count IS '场景数量';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.cell_count IS '小区数量';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.traffic_count IS '业务量统计值';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.poor_scene_count IS '质差场景数量';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.poor_cell_count IS '质差小区数量';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.busy_cell_count IS '超忙小区数量';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.region_wkt IS '区域 WKT，EPSG:4326';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.region_geom IS '区域 MultiPolygon 几何列';
COMMENT ON COLUMN dmk.tm_poor_region_metric_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_poor_region_metric_query ON dmk.tm_poor_region_metric_m(year_month, region_level, provincecode, citycode, stat_type, poor_type, metric_group, scene_type, network_class, metric_code, rank_no);
CREATE INDEX IF NOT EXISTS idx_tm_poor_region_metric_geom ON dmk.tm_poor_region_metric_m USING gist(region_geom) WHERE region_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_poor_scene_list_m (
    year_month varchar(7) NOT NULL,
    poor_scene_id varchar(64) NOT NULL,
    poor_type varchar(32) NOT NULL,
    metric_group varchar(64) NOT NULL,
    scene_id varchar(64) NOT NULL,
    scene_name varchar(128) NOT NULL,
    scene_type varchar(64) NOT NULL,
    scene_type_name varchar(128),
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    center_lon numeric(10, 6),
    center_lat numeric(10, 6),
    bbox numeric(10, 6)[],
    aoi_wkt text,
    aoi_geom geometry(MultiPolygon, 4326) GENERATED ALWAYS AS (
        CASE WHEN aoi_wkt IS NULL THEN NULL
             ELSE ST_Multi(ST_GeomFromText(aoi_wkt, 4326))::geometry(MultiPolygon, 4326)
        END
    ) STORED,
    avg_rsrp numeric(10, 4),
    avg_sinr numeric(10, 4),
    grid_count bigint,
    weak_grid_count bigint,
    weak_grid_ratio numeric(12, 6),
    grid_cover_rate numeric(12, 6),
    mr_cover_rate numeric(12, 6),
    total_user_count bigint,
    total_traffic_gb numeric(18, 4),
    voice_drop_rate numeric(12, 6),
    perception_score numeric(12, 6),
    rank_no integer,
    poor_reason varchar(128),
    poor_reason_name varchar(128),
    update_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, poor_scene_id)
);

COMMENT ON TABLE dmk.tm_poor_scene_list_m IS '质差场景清单月表，支撑清单查询、排序和导出。';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.poor_scene_id IS '质差场景记录 ID';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.poor_type IS '质差类型';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.metric_group IS '指标分组';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.scene_id IS '场景 ID';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.scene_name IS '场景名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.scene_type IS '场景类型编码';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.scene_type_name IS '场景类型名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.center_lon IS '场景中心点经度';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.center_lat IS '场景中心点纬度';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.bbox IS '场景外接矩形';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.aoi_wkt IS '场景 AOI WKT';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.aoi_geom IS '场景 AOI MultiPolygon 几何列';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.avg_rsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.avg_sinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.grid_count IS '场景内栅格数';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.weak_grid_count IS '弱覆盖栅格数';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.weak_grid_ratio IS '弱覆盖栅格占比';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.grid_cover_rate IS '栅格覆盖率';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.mr_cover_rate IS 'MR 覆盖率';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.total_user_count IS '场景内总用户数';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.total_traffic_gb IS '总流量（GB）';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.voice_drop_rate IS '语音掉话率';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.perception_score IS '感知评分';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.rank_no IS '清单内排名';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.poor_reason IS '质差原因编码';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.poor_reason_name IS '质差原因名称';
COMMENT ON COLUMN dmk.tm_poor_scene_list_m.update_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_poor_scene_list_query ON dmk.tm_poor_scene_list_m(year_month, provincecode, citycode, scene_type, rank_no, grid_cover_rate);
CREATE INDEX IF NOT EXISTS idx_tm_poor_scene_list_geom ON dmk.tm_poor_scene_list_m USING gist(aoi_geom) WHERE aoi_geom IS NOT NULL;

CREATE TABLE IF NOT EXISTS dmk.tm_poor_cell_list_m (
    year_month varchar(7) NOT NULL,
    poor_cell_id varchar(64) NOT NULL,
    poor_type varchar(32) NOT NULL,
    metric_group varchar(64),
    cellkey varchar(64) NOT NULL,
    cell_name varchar(128),
    operator_name varchar(32),
    network_class varchar(32) NOT NULL,
    provincecode integer,
    province_name varchar(64),
    citycode integer,
    city_name varchar(64),
    districtcode integer,
    district_name varchar(64),
    scene_id varchar(64),
    scene_name varchar(128),
    scene_type varchar(64),
    scene_type_name varchar(128),
    cell_lon numeric(10, 6),
    cell_lat numeric(10, 6),
    cell_wkt text,
    cell_geom geometry(Point, 4326) GENERATED ALWAYS AS (
        CASE WHEN cell_wkt IS NOT NULL THEN ST_GeomFromText(cell_wkt, 4326)::geometry(Point, 4326)
             WHEN cell_lon IS NOT NULL AND cell_lat IS NOT NULL THEN ST_SetSRID(ST_MakePoint(cell_lon, cell_lat), 4326)::geometry(Point, 4326)
             ELSE NULL
        END
    ) STORED,
    rsrpcount bigint,
    avg_rsrp numeric(10, 4),
    avg_sinr numeric(10, 4),
    weakcover_mrcount bigint,
    busy_user_count bigint,
    traffic_total numeric(18, 4),
    total_traffic_gb numeric(18, 4),
    voice_drop_rate numeric(12, 6),
    perception_score numeric(12, 6),
    rank_no integer,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (year_month, poor_cell_id)
);

COMMENT ON TABLE dmk.tm_poor_cell_list_m IS '质差小区/超忙小区清单月表，保留 traffic_total 与 total_traffic_gb 兼容导出。';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.year_month IS '账期，格式 YYYY-MM';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.poor_cell_id IS '质差小区记录 ID';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.poor_type IS '质差类型（质差/超忙等）';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.metric_group IS '指标分组';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cellkey IS '小区唯一键 cellkey';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cell_name IS '小区名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.operator_name IS '运营商名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.network_class IS '网络制式';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.provincecode IS '省编码';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.province_name IS '省名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.citycode IS '地市编码';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.city_name IS '地市名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.districtcode IS '区县编码';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.district_name IS '区县名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.scene_id IS '关联场景 ID';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.scene_name IS '关联场景名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.scene_type IS '关联场景类型编码';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.scene_type_name IS '关联场景类型名称';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cell_lon IS '小区经度';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cell_lat IS '小区纬度';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cell_wkt IS '小区点 WKT';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.cell_geom IS '小区点几何列';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.rsrpcount IS 'MR 采样数';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.avg_rsrp IS '平均 RSRP（dBm）';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.avg_sinr IS '平均 SINR（dB）';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.weakcover_mrcount IS '弱覆盖采样数';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.busy_user_count IS '忙时用户数';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.traffic_total IS '业务量原始单位（兼容导出口径）';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.total_traffic_gb IS '总流量（GB）';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.voice_drop_rate IS '语音掉话率';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.perception_score IS '感知评分';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.rank_no IS '清单内排名';
COMMENT ON COLUMN dmk.tm_poor_cell_list_m.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_poor_cell_list_query ON dmk.tm_poor_cell_list_m(year_month, provincecode, citycode, poor_type, network_class, rank_no);
CREATE INDEX IF NOT EXISTS idx_tm_poor_cell_list_geom ON dmk.tm_poor_cell_list_m USING gist(cell_geom) WHERE cell_geom IS NOT NULL;

-- =========================================================
-- 7. 导出任务表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.tm_export_task (
    task_id varchar(64) PRIMARY KEY,
    account_id varchar(64) NOT NULL,
    tenant varchar(64),
    biz_module varchar(64) NOT NULL,
    export_type varchar(64),
    request_param jsonb,
    export_columns varchar(128)[] NOT NULL DEFAULT ARRAY[]::varchar[],
    status varchar(32) NOT NULL,
    progress integer NOT NULL DEFAULT 0,
    file_name varchar(256),
    file_url text,
    file_size bigint,
    error_msg text,
    created_time timestamp without time zone NOT NULL DEFAULT now(),
    finish_time timestamp without time zone,
    updated_time timestamp without time zone NOT NULL DEFAULT now(),
    CHECK (status IN ('pending', 'processing', 'running', 'success', 'failed', 'canceled')),
    CHECK (progress BETWEEN 0 AND 100)
);

COMMENT ON TABLE dmk.tm_export_task IS '异步导出任务表，支撑导出详情、列表、取消、下载接口。';
COMMENT ON COLUMN dmk.tm_export_task.task_id IS '导出任务 ID';
COMMENT ON COLUMN dmk.tm_export_task.account_id IS '发起账号 ID';
COMMENT ON COLUMN dmk.tm_export_task.tenant IS '租户标识';
COMMENT ON COLUMN dmk.tm_export_task.biz_module IS '业务模块（building/scene/cluster/poor 等）';
COMMENT ON COLUMN dmk.tm_export_task.export_type IS '导出类型（excel/csv 等）';
COMMENT ON COLUMN dmk.tm_export_task.request_param IS '导出请求参数 JSON 快照';
COMMENT ON COLUMN dmk.tm_export_task.export_columns IS '导出字段列表（数组）';
COMMENT ON COLUMN dmk.tm_export_task.status IS '任务状态：pending/processing/running/success/failed/canceled';
COMMENT ON COLUMN dmk.tm_export_task.progress IS '任务进度（0-100）';
COMMENT ON COLUMN dmk.tm_export_task.file_name IS '导出文件名';
COMMENT ON COLUMN dmk.tm_export_task.file_url IS '导出文件下载地址';
COMMENT ON COLUMN dmk.tm_export_task.file_size IS '导出文件大小（字节）';
COMMENT ON COLUMN dmk.tm_export_task.error_msg IS '错误信息（失败时填充）';
COMMENT ON COLUMN dmk.tm_export_task.created_time IS '任务创建时间';
COMMENT ON COLUMN dmk.tm_export_task.finish_time IS '任务完成时间';
COMMENT ON COLUMN dmk.tm_export_task.updated_time IS '记录更新时间';
CREATE INDEX IF NOT EXISTS idx_tm_export_task_query ON dmk.tm_export_task(account_id, biz_module, status, created_time DESC);