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

create extension if not exists "uuid-ossp";

create schema if not exists nsl;

alter table nsl.simu rename to tbl_yj_tr_simulate;
alter table nsl.region rename to tbl_yj_tr_region;
---------------------------------------------- 1.推演模拟
-- drop table if exists nsl.tbl_yj_tr_simulate;
create table nsl.tbl_yj_tr_simulate (
  	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.tbl_yj_tr_simulate using GIST (geom);

comment on table nsl.tbl_yj_tr_simulate is '推演模拟表';
comment on column nsl.tbl_yj_tr_simulate.id is '主键ID';
comment on column nsl.tbl_yj_tr_simulate.name is '名称';
comment on column nsl.tbl_yj_tr_simulate.service_name is '服务名称';
comment on column nsl.tbl_yj_tr_simulate.type is '类别：1-预测模拟，2-实时模拟，3-历史模拟';
comment on column nsl.tbl_yj_tr_simulate.area_type is '区域类别：0-自定义，1-行政区划，2-重点区域，3-重点沟';
comment on column nsl.tbl_yj_tr_simulate.area_name is '区域名称';
comment on column nsl.tbl_yj_tr_simulate.data is '数据(JSON)';
comment on column nsl.tbl_yj_tr_simulate.status is '状态：0-创建仿真，1-预处理，2-分析中，10-完成，20-出错';
comment on column nsl.tbl_yj_tr_simulate.result is '结果(JSON)';
comment on column nsl.tbl_yj_tr_simulate.create_time is '创建时间';
comment on column nsl.tbl_yj_tr_simulate.create_user is '创建人';
comment on column nsl.tbl_yj_tr_simulate.update_time is '更新时间';
comment on column nsl.tbl_yj_tr_simulate.update_user is '更新人';
comment on column nsl.tbl_yj_tr_simulate.bak is '备注';
comment on column nsl.tbl_yj_tr_simulate.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.tbl_yj_tr_simulate where id > 0 order by id desc;
-- update nsl.tbl_yj_tr_simulate set status=0,area_name='房山区',result=null,data = '{"startTime":"2025-05-01 11:10:54","type":3,"total":150,"duration":6,"intensity":60,"history":"XX年50mm降雨"}' where id = 17;
-- update nsl.tbl_yj_tr_simulate set status=0,area_name='房山区',result=null,data = '{"startTime":"2025-05-06 14:31:58","type":3,"total":1000,"duration":6,"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}]}' where id = 17;
---------------------------------------------- 2.推演区域
-- drop table if exists nsl.tbl_yj_tr_region;
create table nsl.tbl_yj_tr_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.tbl_yj_tr_region using GIST (geom);

comment on table nsl.tbl_yj_tr_region is '推演区域表';
comment on column nsl.tbl_yj_tr_region.id is '主键ID';
comment on column nsl.tbl_yj_tr_region.name is '名称';
comment on column nsl.tbl_yj_tr_region.type is '类别：1-行政区划，2-重点区域，3-重点沟';
comment on column nsl.tbl_yj_tr_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.tbl_yj_tr_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;

---------------------------------------------- 雪花主键
create sequence nsl.seq_tab_id
   start with 0   -- 序列值开始值
   increment by 1 -- 步长
   minvalue 0     -- 最小值
   no maxvalue    -- 为了保证 序列有序， 如果设定了最大值，则要打开cycle
   cache 100;

create or replace function nsl.fn_generate_snow_id(input_machine_id integer)
   returns bigint as $$
declare
   -- our_epoch bigint := 1672502400000; --2023-1-1 00:00:00 的时间戳 , 修改基准时间为2023-01-01. 这样就可以使用到 2023 +69 = 2092年。
   seq_id bigint := 0;
   machine_id int := 0;
   now_millis bigint := 0;
   result bigint := 0;
begin
   seq_id := nextval('nsl.seq_tab_id') % 4096; -- 序列号值大于4096，需要循环
   machine_id := input_machine_id % 1024 ;
   -- select floor(extract(epoch from clock_timestamp()) * 1000) into now_millis;
   -- result := result | ((now_millis - our_epoch) << 22) ; -- 调整基准时间的实现
   select floor(extract(epoch from clock_timestamp()) * 1000) into now_millis;
   result := result | (now_millis  << 22) ;
   result := result | (machine_id << 10) ;
   result := result | (seq_id & 4095);  
   
   return result;
end;
$$ language plpgsql;

select nsl.fn_generate_snow_id(32);
----------------------------------------------