-- 确保使用UTF-8编码
SET CLIENT_ENCODING TO 'UTF8';

-- 重新创建表，使用正确的编码
DROP TABLE IF EXISTS "PROJECT";
CREATE TABLE "PROJECT" (
  "project_id" BIGINT NOT NULL IDENTITY(1,1),
  "project_name" VARCHAR(100) NOT NULL,
  "project_category" VARCHAR(50) NOT NULL,
  "project_subcategory" VARCHAR(50) NOT NULL,
  "project_description" VARCHAR(500) DEFAULT NULL,
  "project_url" VARCHAR(255) DEFAULT NULL,
  "status" CHAR(1) NOT NULL DEFAULT '0',
  "create_by" VARCHAR(64) DEFAULT '',
  "create_time" DATETIME,
  "update_by" VARCHAR(64) DEFAULT '',
  "update_time" DATETIME,
  "remark" VARCHAR(500) DEFAULT NULL,
  PRIMARY KEY ("project_id")
);

-- 添加约束
ALTER TABLE "PROJECT" ADD CONSTRAINT "project_check_1" CHECK ("status" IN ('0','1','2'));

-- 添加中文注释，确保编码正确
COMMENT ON TABLE "PROJECT" IS '项目表';
COMMENT ON COLUMN "PROJECT"."project_id" IS '项目ID';
COMMENT ON COLUMN "PROJECT"."project_name" IS '项目名称';
COMMENT ON COLUMN "PROJECT"."project_category" IS '项目分类（数字孪生、水利信息化等）';
COMMENT ON COLUMN "PROJECT"."project_subcategory" IS '项目子类（数字孪生太浦河、数字孪生水文站等）';
COMMENT ON COLUMN "PROJECT"."project_description" IS '项目描述';
COMMENT ON COLUMN "PROJECT"."project_url" IS '项目访问URL';
COMMENT ON COLUMN "PROJECT"."status" IS '项目状态（0正常 1维护中 2已删除）';
COMMENT ON COLUMN "PROJECT"."create_by" IS '创建者';
COMMENT ON COLUMN "PROJECT"."create_time" IS '创建时间';
COMMENT ON COLUMN "PROJECT"."update_by" IS '更新者';
COMMENT ON COLUMN "PROJECT"."update_time" IS '更新时间';
COMMENT ON COLUMN "PROJECT"."remark" IS '备注';

-- 插入数据
INSERT INTO "PROJECT" ("project_name", "project_category", "project_subcategory", "project_description", "status", "create_by", "create_time", "update_by", "update_time") VALUES
('数字孪生太浦河', '数字孪生', '数字孪生太浦河', '基于数字孪生技术的太浦河流域综合管理平台，实现河流监测、模拟预测与智能调度', '0', 'admin', '2024-06-15 00:00:00', 'admin', '2024-06-15 00:00:00'),
('数字孪生太浦闸', '数字孪生', '数字孪生太浦河', '基于数字孪生技术的太浦闸智能监控系统，实现闸门运行状态实时监测与调度控制', '0', 'admin', '2024-06-20 00:00:00', 'admin', '2024-06-20 00:00:00'),
('贡湖实验站', '数字孪生', '数字孪生水文站', '实现水文监测站点的数字化映射，提供实时数据可视化与智能分析功能', '0', 'admin', '2024-03-10 00:00:00', 'admin', '2024-03-10 00:00:00'),
('太师桥水文站', '数字孪生', '数字孪生水文站', '全流域水文数据采集与传输系统，支持多源数据融合与标准化管理', '0', 'admin', '2024-03-15 00:00:00', 'admin', '2024-03-15 00:00:00'),
('铜山源灌区', '数字孪生', '数字孪生灌区', '基于数字孪生技术的灌区水资源优化配置系统，实现精准灌溉与水资源高效利用', '0', 'admin', '2024-04-20 00:00:00', 'admin', '2024-04-20 00:00:00'),
('水资源评价调配', '水利信息化', '水资源评价调配', '水资源评价调配模型是宁波 "库群互联、三网协同" 智慧水网体系的核心技术支撑', '0', 'admin', '2024-05-10 00:00:00', 'admin', '2024-05-10 00:00:00'),
('流域数据采集平台', '水利信息化', '流域数据采集平台', '多参数水质在线监测系统，实现水质指标实时采集、分析与可视化，支持水质预警和决策辅助', '0', 'admin', '2024-05-15 00:00:00', 'admin', '2024-05-15 00:00:00'),
('千岛湖水资源评价', '水利信息化', '千岛湖水资源评价', '基于多源数据的千岛湖水资源综合评价系统，实现水资源量、质、效的全面监测与分析', '0', 'admin', '2024-06-01 00:00:00', 'admin', '2024-06-01 00:00:00');