-- 1. 创建表 (id 使用 BIGSERIAL 实现自增)
CREATE TABLE "vector"."jc_urban_expansion" (
    id              BIGSERIAL PRIMARY KEY,             -- 自增主键
    year            INTEGER NOT NULL,                  -- 年份
    layer_type      VARCHAR(20) NOT NULL,              -- 图层类型: 'boundary' 或 'expand'
    dim_type        VARCHAR(10) NOT NULL DEFAULT '2d', -- 维度类型: '2d' 或 '3d'
    layer_name      VARCHAR(100) NOT NULL,             -- 图层名称(对应DataSource)
    prev_year       INTEGER,                           -- 上一年份
    data            NUMERIC(18,2),                     -- 数据(面积/数量)
    service_id      VARCHAR(50),                       -- 服务ID(如 map-city)
    sort_order      INTEGER DEFAULT 0,                 -- 排序
    status          SMALLINT DEFAULT 1,                -- 状态: 1-启用, 0-禁用
    create_time     TIMESTAMP DEFAULT NOW(),           -- 创建时间
    update_time     TIMESTAMP DEFAULT NOW(),           -- 更新时间
    remark          VARCHAR(500)                       -- 备注
);

-- 2. 添加注释
COMMENT ON TABLE "vector"."jc_urban_expansion" IS '城市扩张图层配置表';
COMMENT ON COLUMN "vector"."jc_urban_expansion".id IS '自增主键ID';
COMMENT ON COLUMN "vector"."jc_urban_expansion".year IS '年份';
COMMENT ON COLUMN "vector"."jc_urban_expansion".layer_type IS '图层类型: boundary-边界, expand-扩张';
COMMENT ON COLUMN "vector"."jc_urban_expansion".dim_type IS '维度类型: 2d-二维, 3d-三维';
COMMENT ON COLUMN "vector"."jc_urban_expansion".layer_name IS '图层名称';
COMMENT ON COLUMN "vector"."jc_urban_expansion".prev_year IS '上一年份';
COMMENT ON COLUMN "vector"."jc_urban_expansion".data IS '统计数据';
COMMENT ON COLUMN "vector"."jc_urban_expansion".service_id IS '服务ID';
COMMENT ON COLUMN "vector"."jc_urban_expansion".sort_order IS '排序';
COMMENT ON COLUMN "vector"."jc_urban_expansion".status IS '状态';

-- 3. 创建索引
CREATE INDEX idx_urban_exp_year ON "vector"."jc_urban_expansion"(year);
CREATE INDEX idx_urban_exp_type ON "vector"."jc_urban_expansion"(layer_type, dim_type);

-- 4. 插入示例数据 (需将 service_id 设为 map-city)
INSERT INTO "vector"."jc_urban_expansion" (year, layer_type, dim_type, layer_name, prev_year, data, service_id, sort_order) VALUES
-- 边界图层
(2009, 'boundary', '2d', 'boundary_2009@DataSource', NULL, 125.36, 'map-city', 1),
(2010, 'boundary', '2d', 'boundary_2010@DataSource', NULL, 132.58, 'map-city', 2),
(2011, 'boundary', '2d', 'boundary_2011@DataSource', NULL, 145.20, 'map-city', 3),
(2012, 'boundary', '2d', 'boundary_2012@DataSource', NULL, 156.80, 'map-city', 4),
(2013, 'boundary', '2d', 'boundary_2013@DataSource', NULL, 168.45, 'map-city', 5),
(2014, 'boundary', '2d', 'boundary_2014@DataSource', NULL, 182.30, 'map-city', 6),
(2015, 'boundary', '2d', 'boundary_2015@DataSource', NULL, 195.60, 'map-city', 7),
(2016, 'boundary', '2d', 'boundary_2016@DataSource', NULL, 210.85, 'map-city', 8),
(2017, 'boundary', '2d', 'boundary_2017@DataSource', NULL, 228.40, 'map-city', 9),
(2018, 'boundary', '2d', 'boundary_2018@DataSource', NULL, 245.75, 'map-city', 10),
(2019, 'boundary', '2d', 'boundary_2019@DataSource', NULL, 262.30, 'map-city', 11),
-- 扩张图层
(2010, 'expand', '2d', 'expand_2009_2010@DataSource', 2009, 7.22, 'map-city', 1),
(2011, 'expand', '2d', 'expand_2010_2011@DataSource', 2010, 12.62, 'map-city', 2),
(2012, 'expand', '2d', 'expand_2011_2012@DataSource', 2011, 11.60, 'map-city', 3),
(2013, 'expand', '2d', 'expand_2012_2013@DataSource', 2012, 11.65, 'map-city', 4),
(2014, 'expand', '2d', 'expand_2013_2014@DataSource', 2013, 13.85, 'map-city', 5),
(2015, 'expand', '2d', 'expand_2014_2015@DataSource', 2014, 13.30, 'map-city', 6),
(2016, 'expand', '2d', 'expand_2015_2016@DataSource', 2015, 15.25, 'map-city', 7),
(2017, 'expand', '2d', 'expand_2016_2017@DataSource', 2016, 17.55, 'map-city', 8),
(2018, 'expand', '2d', 'expand_2017_2018@DataSource', 2017, 17.35, 'map-city', 9),
(2019, 'expand', '2d', 'expand_2018_2019@DataSource', 2018, 16.55, 'map-city', 10);