-- ============================================
-- FTP已处理文件记录表（普通表）
-- 用于记录已处理过的FTP txt文件，避免重复处理
-- ============================================

CREATE TABLE "mid_voice"."mid_ftp_processed_file" (
    "id" int8 NOT NULL GENERATED ALWAYS AS IDENTITY (
        INCREMENT 1
        MINVALUE  1
        MAXVALUE 9223372036854775807
        START 1
        CACHE 1
    ),
    "city_code" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
    "city_name" varchar(100) COLLATE "pg_catalog"."default",
    "file_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
    "file_size" int8,
    "process_status" varchar(10) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '1',
    "record_count" int4 DEFAULT 0,
    "success_count" int4 DEFAULT 0,
    "fail_count" int4 DEFAULT 0,
    "process_time" int8 DEFAULT 0,
    "error_msg" text COLLATE "pg_catalog"."default",
    "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "mid_ftp_processed_file_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "uk_ftp_file_city_name" UNIQUE ("city_code", "file_name")
);

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

-- 创建索引
CREATE INDEX "idx_ftp_processed_file_name" ON "mid_voice"."mid_ftp_processed_file" USING btree (
    "file_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

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

-- 添加注释
COMMENT ON TABLE "mid_voice"."mid_ftp_processed_file" IS 'FTP已处理文件记录表';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."id" IS '自增ID主键';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."city_code" IS '地市编码';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."city_name" IS '地市名称';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."file_name" IS 'FTP文件名';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."file_size" IS '文件大小（字节）';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."process_status" IS '处理状态：0失败，1成功';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."record_count" IS '记录总数';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."success_count" IS '成功入库数';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."fail_count" IS '失败数';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."process_time" IS '处理耗时（毫秒）';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."error_msg" IS '错误信息';
COMMENT ON COLUMN "mid_voice"."mid_ftp_processed_file"."create_time" IS '创建时间';