-- ============================================
-- 设备连接日志表
-- 用于记录设备连接失败信息，便于排查问题
-- ============================================

CREATE TABLE "mid_voice"."mid_voice_device_log" (
    "id" int8 NOT NULL GENERATED ALWAYS AS IDENTITY (
        INCREMENT 1
        MINVALUE  1
        MAXVALUE 9223372036854775807
        START 1
        CACHE 1
    ),
    "device_id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
    "device_no" varchar(100) COLLATE "pg_catalog"."default",
    "city_code" varchar(20) COLLATE "pg_catalog"."default",
    "city_name" varchar(100) COLLATE "pg_catalog"."default",
    "ip_address" varchar(50) COLLATE "pg_catalog"."default",
    "device_port" int4,
    "connect_type" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
    "connect_status" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
    "fail_reason" text COLLATE "pg_catalog"."default",
    "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "mid_voice_device_log_pkey" PRIMARY KEY ("id")
)
PARTITION BY LIST (
    "city_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);

ALTER TABLE "mid_voice"."mid_voice_device_log"
    OWNER TO "dcms_dev";

-- 创建索引
CREATE INDEX "idx_device_log_device_id" ON "mid_voice"."mid_voice_device_log" USING btree (
    "device_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "idx_device_log_create_time" ON "mid_voice"."mid_voice_device_log" USING btree (
    "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

CREATE INDEX "idx_device_log_status" ON "mid_voice"."mid_voice_device_log" USING btree (
    "connect_status" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- 创建分区表（16个地市）
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3401" FOR VALUES IN ('340100');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3402" FOR VALUES IN ('340200');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3403" FOR VALUES IN ('340300');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3404" FOR VALUES IN ('340400');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3405" FOR VALUES IN ('340500');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3406" FOR VALUES IN ('340600');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3407" FOR VALUES IN ('340700');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3408" FOR VALUES IN ('340800');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3410" FOR VALUES IN ('341000');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3411" FOR VALUES IN ('341100');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3412" FOR VALUES IN ('341200');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3413" FOR VALUES IN ('341300');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3415" FOR VALUES IN ('341500');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3416" FOR VALUES IN ('341600');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3417" FOR VALUES IN ('341700');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_3418" FOR VALUES IN ('341800');
ALTER TABLE "mid_voice"."mid_voice_device_log" ATTACH PARTITION "mid_voice"."mid_voice_device_log_default" DEFAULT;

-- 添加注释
COMMENT ON TABLE "mid_voice"."mid_voice_device_log" IS '设备连接日志表';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."id" IS '自增ID主键';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."device_id" IS '设备ID';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."device_no" IS '设备编号（UUID）';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."city_code" IS '地市编码';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."city_name" IS '地市名称';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."ip_address" IS 'IP地址';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."device_port" IS '端口号';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."connect_type" IS '连接类型：1登录，2查询录音列表，3下载文件';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."connect_status" IS '连接状态：0失败，1成功';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."fail_reason" IS '失败原因';
COMMENT ON COLUMN "mid_voice"."mid_voice_device_log"."create_time" IS '创建时间';