---------------------------------------------- 0.初始化
create extension if not exists postgis cascade;

create extension if not exists "uuid-ossp";

create schema if not exists nsl;
---------------------------------------------- 1.推演模拟
-- drop table if exists nsl.simu;
create table nsl.simu (
  	id serial primary key,
  	name varchar(200),
  	service_name varchar(200),
    type smallint default 1,
	area_type smallint default 0,
	area_name varchar(200),
	data varchar(8000),
	status smallint default 0,
	result varchar(8000),
	create_time timestamp(6) without time zone default now(),
	create_user varchar(50),
	update_time timestamp(6) without time zone,
  	update_user varchar(50),
	bak varchar(2000),
	geom geometry(MultiPolygon, 4490)
);
-- drop index nsl.idx_nsl_simu_geom;
create index idx_nsl_simu_geom on nsl.simu using GIST (geom);

comment on table nsl.simu is '推演模拟表';
comment on column nsl.simu.id is '主键ID';
comment on column nsl.simu.name is '名称';
comment on column nsl.simu.service_name is '服务名称';
comment on column nsl.simu.type is '类别：1-预测模拟，2-实时模拟，3-历史模拟';
comment on column nsl.simu.area_type is '区域类别：0-自定义，1-行政区划，2-重点区域，3-重点沟';
comment on column nsl.simu.area_name is '区域名称';
comment on column nsl.simu.data is '数据(JSON)';
comment on column nsl.simu.status is '状态：0-创建仿真，1-预处理，2-分析中，10-完成，20-出错';
comment on column nsl.simu.result is '结果(JSON)';
comment on column nsl.simu.create_time is '创建时间';
comment on column nsl.simu.create_user is '创建人';
comment on column nsl.simu.update_time is '更新时间';
comment on column nsl.simu.update_user is '更新人';
comment on column nsl.simu.bak is '备注';
comment on column nsl.simu.geom is '空间位置';

-- insert into nsl.simu (name, service_name, type, area_type, area_name, status, geom) values ('预测模拟-0416', '20250416100000', 1, 1, '大兴区', 0, ST_GeomFromText('MULTIPOLYGON(((116.666748 39.639859,116.659403 39.626287,116.665956 39.654698,116.666748 39.639859)))'));

select id,name,service_name,type,area_type,area_name,status,result,create_time,update_time,bak,data,st_astext(geom) from nsl.simu where id > 0 order by id desc;
-- update nsl.simu set status=0,area_name='房山区',result=null,data = '{"startTime":"2025-05-01 11:10:54","type":3,"total":50,"duration":6,"intensity":70,"history":"XX年50mm降雨"}' where id = 17;
-- {"startTime":"2025-05-01 10:51:33","type":3,"total":1025,"duration":96,"intensity":78.5,"rainfalls":[{"time":"2023-07-29 22:00:00","intensity":"2","total":"2"},{"time":"2023-07-29 23:00:00","intensity":"2","total":"4"},{"time":"2023-07-30 00:00:00","intensity":"3.5","total":"7.5"},{"time":"2023-07-30 01:00:00","intensity":"2.5","total":"10"},{"time":"2023-07-30 02:00:00","intensity":"2","total":"244.5"},{"time":"2023-07-30 03:00:00","intensity":"3.5","total":"339.5"},{"time":"2023-07-30 04:00:00","intensity":"6","total":"345.5"},{"time":"2023-07-30 05:00:00","intensity":"10.5","total":"356"},{"time":"2023-07-30 06:00:00","intensity":"4.5","total":"360.5"},{"time":"2023-07-30 07:00:00","intensity":"5.5","total":"366"},{"time":"2023-07-30 08:00:00","intensity":"13","total":"379"},{"time":"2023-07-30 09:00:00","intensity":"17","total":"396"},{"time":"2023-07-30 10:00:00","intensity":"15.5","total":"25.5"},{"time":"2023-07-30 11:00:00","intensity":"17.5","total":"43"},{"time":"2023-07-30 12:00:00","intensity":"16","total":"59"},{"time":"2023-07-30 13:00:00","intensity":"18.5","total":"77.5"},{"time":"2023-07-30 14:00:00","intensity":"13","total":"90.5"},{"time":"2023-07-30 15:00:00","intensity":"23.5","total":"114"},{"time":"2023-07-30 16:00:00","intensity":"28.5","total":"142.5"},{"time":"2023-07-30 17:00:00","intensity":"24.5","total":"167"},{"time":"2023-07-30 18:00:00","intensity":"43","total":"210"},{"time":"2023-07-30 19:00:00","intensity":"32.5","total":"242.5"},{"time":"2023-07-30 20:00:00","intensity":"49.5","total":"294"},{"time":"2023-07-30 21:00:00","intensity":"18.5","total":"312.5"},{"time":"2023-07-30 22:00:00","intensity":"8","total":"320.5"},{"time":"2023-07-30 23:00:00","intensity":"15.5","total":"336"},{"time":"2023-07-31 00:00:00","intensity":"12.5","total":"408.5"},{"time":"2023-07-31 01:00:00","intensity":"15","total":"423.5"},{"time":"2023-07-31 02:00:00","intensity":"4","total":"537"},{"time":"2023-07-31 03:00:00","intensity":"12","total":"647"},{"time":"2023-07-31 04:00:00","intensity":"5","total":"652"},{"time":"2023-07-31 05:00:00","intensity":"36.5","total":"688.5"},{"time":"2023-07-31 06:00:00","intensity":"18","total":"706.5"},{"time":"2023-07-31 07:00:00","intensity":"26","total":"732.5"},{"time":"2023-07-31 08:00:00","intensity":"38","total":"770.5"},{"time":"2023-07-31 09:00:00","intensity":"39.5","total":"810"},{"time":"2023-07-31 10:00:00","intensity":"38","total":"461.5"},{"time":"2023-07-31 11:00:00","intensity":"27","total":"488.5"},{"time":"2023-07-31 12:00:00","intensity":"5.5","total":"494"},{"time":"2023-07-31 13:00:00","intensity":"2.5","total":"496.5"},{"time":"2023-07-31 14:00:00","intensity":"4","total":"500.5"},{"time":"2023-07-31 15:00:00","intensity":"8","total":"508.5"},{"time":"2023-07-31 16:00:00","intensity":"8.5","total":"517"},{"time":"2023-07-31 17:00:00","intensity":"0.5","total":"517.5"},{"time":"2023-07-31 18:00:00","intensity":"10","total":"527.5"},{"time":"2023-07-31 19:00:00","intensity":"5.5","total":"533"},{"time":"2023-07-31 20:00:00","intensity":"20.5","total":"557.5"},{"time":"2023-07-31 21:00:00","intensity":"25","total":"582.5"},{"time":"2023-07-31 22:00:00","intensity":"25.5","total":"608"},{"time":"2023-07-31 23:00:00","intensity":"27","total":"635"},{"time":"2023-08-01 00:00:00","intensity":"78.5","total":"888.5"},{"time":"2023-08-01 01:00:00","intensity":"32.5","total":"921"},{"time":"2023-08-01 02:00:00","intensity":"9.5","total":"956.5"},{"time":"2023-08-01 03:00:00","intensity":"6.5","total":"981.5"},{"time":"2023-08-01 04:00:00","intensity":"22.5","total":"1004"},{"time":"2023-08-01 05:00:00","intensity":"1","total":"1005"},{"time":"2023-08-01 06:00:00","intensity":"0","total":"1005"},{"time":"2023-08-01 07:00:00","intensity":"8","total":"1013"},{"time":"2023-08-01 08:00:00","intensity":"6","total":"1019"},{"time":"2023-08-01 09:00:00","intensity":"5.5","total":"1024.5"},{"time":"2023-08-01 10:00:00","intensity":"3","total":"924"},{"time":"2023-08-01 11:00:00","intensity":"5.5","total":"929.5"},{"time":"2023-08-01 12:00:00","intensity":"8.5","total":"938"},{"time":"2023-08-01 13:00:00","intensity":"8","total":"946"},{"time":"2023-08-01 14:00:00","intensity":"0.5","total":"946.5"},{"time":"2023-08-01 16:00:00","intensity":"0","total":"946.5"},{"time":"2023-08-01 18:00:00","intensity":"0.5","total":"947"},{"time":"2023-08-01 20:00:00","intensity":"17","total":"973.5"},{"time":"2023-08-01 21:00:00","intensity":"1","total":"974.5"},{"time":"2023-08-01 22:00:00","intensity":"0.5","total":"975"},{"time":"2023-08-02 00:00:00","intensity":"0","total":"1024.5"},{"time":"2023-08-02 02:00:00","intensity":"0","total":"1025"},{"time":"2023-08-02 03:00:00","intensity":"0.5","total":"1025.5"},{"time":"2023-08-02 04:00:00","intensity":"0","total":"1025.5"},{"time":"2023-08-02 06:00:00","intensity":"0","total":"1025.5"},{"time":"2023-08-02 08:00:00","intensity":"0","total":"1025.5"},{"time":"2023-08-02 12:00:00","intensity":"0","total":"1024.5"},{"time":"2023-08-02 14:00:00","intensity":"0","total":"1024.5"},{"time":"2023-08-02 15:00:00","intensity":"0.5","total":"1025"},{"time":"2023-08-02 18:00:00","intensity":"0","total":"1025"},{"time":"2023-08-02 20:00:00","intensity":"0","total":"1025"},{"time":"2023-08-02 22:00:00","intensity":"0","total":"1025"}]}
---------------------------------------------- 2.推演区域
-- drop table if exists nsl.region;
create table nsl.region (
  	id serial primary key,
  	name varchar(200),
    type smallint default 1,
	geom geometry(MultiPolygon, 4490)
);
-- drop index nsl.idx_nsl_region_geom;
create index idx_nsl_region_geom on nsl.region using GIST (geom);

comment on table nsl.region is '推演区域表';
comment on column nsl.region.id is '主键ID';
comment on column nsl.region.name is '名称';
comment on column nsl.region.type is '类别：1-行政区划，2-重点区域，3-重点沟';
comment on column nsl.region.geom is '空间位置';

-- insert into nsl.region (name, type, geom) values ('大兴', 1, ST_GeomFromText('MULTIPOLYGON(((116.666748 39.639859,116.659403 39.626287,116.665956 39.654698,116.666748 39.639859)))'));

select id, name, type, st_astext(geom) from nsl.region order by type, id;
---------------------------------------------- 3.雨量计 *
-- drop table if exists nsl.udometer;
create table nsl.udometer (
  	id serial primary key,
  	name varchar(200),
    rainfall numeric(15, 3) default 0.0, -- 总位数/小数位数
	geom geometry(Point, 4490)
);
-- drop index nsl.idx_nsl_udometer_geom;
create index idx_nsl_udometer_geom on nsl.udometer using GIST (geom);

comment on table nsl.udometer is '雨量计表';
comment on column nsl.udometer.id is '主键ID';
comment on column nsl.udometer.name is '名称';
comment on column nsl.udometer.rainfall is '降雨量(米)';
comment on column nsl.udometer.geom is '空间位置';

select * from nsl.udometer;
---------------------------------------------- 将 MULTIPOLYGON 转换为 POLYGON
select st_astext(ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom)))) as geom from nsl.region limit 1;