-- ============================================================
-- 数据库变更记录 (changeSql.sql)
-- 规则:每次表结构/基础数据变更,在文件【末尾追加】一个新块,
--       不要修改或删除历史块。执行时按从上到下顺序依次执行新增部分。
-- 数据库: PostgreSQL (park_bike)
-- ============================================================


-- ------------------------------------------------------------
-- 2026-06-22  v3.0.1  接入抖音券核销 —— 新增抖音商品表
-- 作者: rk
-- 说明: 用于落地抖音「查询商品线上数据列表」接口同步过来的团购商品
-- ------------------------------------------------------------

-- 抖音商品主表
CREATE TABLE "douyin_product" (
  "id"             varchar(64)   NOT NULL,
  "product_id"     varchar(64)   NOT NULL,
  "out_id"         varchar(128),
  "product_name"   varchar(255),
  "category"       varchar(64),
  "product_type"   smallint,
  "online_status"  smallint      DEFAULT 1,
  "account_id"     varchar(64),
  "sync_date"      timestamp,
  "raw_content"    text,
  "create_date"    timestamp     DEFAULT CURRENT_TIMESTAMP,
  "creator"        varchar(64),
  "edit_date"      timestamp,
  "editor"         varchar(64),
  "isdeleted"      smallint      DEFAULT 0,
  PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX uk_douyin_product_product_id ON "douyin_product" ("product_id") WHERE "isdeleted" = 0;
COMMENT ON TABLE  "douyin_product" IS '抖音商品(团购)线上数据';
COMMENT ON COLUMN "douyin_product"."product_id"    IS '抖音商品ID(业务唯一键,用于upsert)';
COMMENT ON COLUMN "douyin_product"."out_id"        IS '外部商品ID';
COMMENT ON COLUMN "douyin_product"."online_status" IS '在线状态 1在线 2下线 3封禁';
COMMENT ON COLUMN "douyin_product"."account_id"    IS '来客商户根账户ID';
COMMENT ON COLUMN "douyin_product"."sync_date"     IS '最近同步时间';
COMMENT ON COLUMN "douyin_product"."raw_content"   IS '抖音原始响应快照(便于追溯)';
COMMENT ON COLUMN "douyin_product"."isdeleted"     IS '是否已删除 0未删除 1已删除';

-- 抖音商品 SKU 子表
CREATE TABLE "douyin_product_sku" (
  "id"             varchar(64)   NOT NULL,
  "product_id"     varchar(64)   NOT NULL,
  "sku_id"         varchar(64),
  "title"          varchar(255),
  "third_sku_id"   varchar(128),
  "sku_out_id"     varchar(128),
  "market_price"   bigint,
  "groupon_type"   smallint,
  "voucher_type"   smallint,
  "create_date"    timestamp     DEFAULT CURRENT_TIMESTAMP,
  "edit_date"      timestamp,
  "isdeleted"      smallint      DEFAULT 0,
  PRIMARY KEY ("id")
);
CREATE INDEX idx_douyin_product_sku_pid ON "douyin_product_sku" ("product_id");
COMMENT ON TABLE  "douyin_product_sku" IS '抖音商品SKU';
COMMENT ON COLUMN "douyin_product_sku"."product_id"   IS '关联 douyin_product.product_id(抖音商品ID)';
COMMENT ON COLUMN "douyin_product_sku"."market_price" IS '市场价(分)';
COMMENT ON COLUMN "douyin_product_sku"."isdeleted"    IS '是否已删除 0未删除 1已删除';


-- ------------------------------------------------------------
-- 2026-06-22  v3.0.1  接入抖音券核销 —— 新增抖音券核销记录表
-- 作者: rk
-- 说明: 记录抖音团购券的验券(核销)/撤销流水,撤销核销与对账依赖本表
-- ------------------------------------------------------------

CREATE TABLE "douyin_verify_record" (
  "id"              varchar(64)   NOT NULL,
  "verify_id"       varchar(64),
  "certificate_id"  varchar(64),
  "order_id"        varchar(64),
  "origin_code"     varchar(128),
  "encrypted_code"  varchar(255),
  "poi_id"          varchar(64),
  "account_id"      varchar(64),
  "product_id"      varchar(64),
  "product_name"    varchar(255),
  "pay_amount"      bigint,
  "verify_status"   smallint      DEFAULT 1,
  "verify_time"     timestamp,
  "verify_user_id"  varchar(64),
  "verify_msg"      varchar(255),
  "cancel_status"   smallint      DEFAULT 0,
  "cancel_time"     timestamp,
  "cancel_user_id"  varchar(64),
  "cancel_msg"      varchar(255),
  "raw_request"     text,
  "raw_response"    text,
  "create_date"     timestamp     DEFAULT CURRENT_TIMESTAMP,
  "creator"         varchar(64),
  "edit_date"       timestamp,
  "editor"          varchar(64),
  "isdeleted"       smallint      DEFAULT 0,
  PRIMARY KEY ("id")
);
CREATE INDEX idx_dvr_verify_id  ON "douyin_verify_record" ("verify_id");
CREATE INDEX idx_dvr_cert_id    ON "douyin_verify_record" ("certificate_id");
CREATE INDEX idx_dvr_order_id   ON "douyin_verify_record" ("order_id");
COMMENT ON TABLE  "douyin_verify_record" IS '抖音券核销记录';
COMMENT ON COLUMN "douyin_verify_record"."verify_id"      IS '验券返回的一次核销唯一标识,撤销必用';
COMMENT ON COLUMN "douyin_verify_record"."certificate_id" IS '券标识,撤销必用';
COMMENT ON COLUMN "douyin_verify_record"."encrypted_code" IS '加密券码(prepare返回,verify入参)';
COMMENT ON COLUMN "douyin_verify_record"."pay_amount"     IS '实付金额(分)';
COMMENT ON COLUMN "douyin_verify_record"."verify_status"  IS '核销结果 0成功 1失败';
COMMENT ON COLUMN "douyin_verify_record"."cancel_status"  IS '撤销状态 0未撤销 1已撤销';
COMMENT ON COLUMN "douyin_verify_record"."raw_request"    IS '请求快照(便于追溯)';
COMMENT ON COLUMN "douyin_verify_record"."raw_response"   IS '响应快照(便于追溯)';
COMMENT ON COLUMN "douyin_verify_record"."isdeleted"      IS '是否已删除 0未删除 1已删除';


-- ------------------------------------------------------------
-- 2026-06-25  v3.0.1  抖音商品 out_id 语义变更 —— 改由管理端绑定本地套餐
-- 作者: rk
-- 说明: out_id 不再由抖音同步写入,改为管理端绑定本地套餐主键(discount.id)。
--       字段结构不变,仅更新列注释(覆盖旧注释,幂等可重复执行);
--       同步逻辑 upsertProduct 已停止用抖音返回的 out_id 覆盖本地值。
-- ------------------------------------------------------------

COMMENT ON COLUMN "douyin_product"."out_id" IS '绑定本地套餐ID(discount.id,管理端维护,抖音同步不写入)';


-- ------------------------------------------------------------
-- 2026-06-25  v3.0.1  抖音验券操作日志表 —— web 端接口操作流水
-- 作者: rk
-- 说明: 记录 web 端 prepare/verify/cancel 每次调用的操作流水(谁/何时/结果/耗时/IP)。
--       完整审计:含请求入参与抖音响应原文;核销业务数据仍由 douyin_verify_record 承载。
-- ------------------------------------------------------------

CREATE TABLE "douyin_verify_log" (
  "id"               varchar(64)   NOT NULL,
  "operate_type"     smallint,
  "api_path"         varchar(64),
  "member_id"        varchar(64),
  "verify_record_id" varchar(64),
  "poi_id"           varchar(64),
  "origin_code"      varchar(128),
  "result"           smallint,
  "error_msg"        varchar(500),
  "raw_request"      text,
  "raw_response"     text,
  "ip"               varchar(64),
  "cost_ms"          integer,
  "create_date"      timestamp     DEFAULT CURRENT_TIMESTAMP,
  "isdeleted"        smallint      DEFAULT 0,
  PRIMARY KEY ("id")
);
CREATE INDEX idx_dvl_member ON "douyin_verify_log" ("member_id");
CREATE INDEX idx_dvl_record ON "douyin_verify_log" ("verify_record_id");
COMMENT ON TABLE  "douyin_verify_log" IS '抖音验券操作日志(web端接口操作流水)';
COMMENT ON COLUMN "douyin_verify_log"."operate_type"     IS '操作类型 0验券准备 1核销 2撤销核销';
COMMENT ON COLUMN "douyin_verify_log"."api_path"         IS '接口路径';
COMMENT ON COLUMN "douyin_verify_log"."member_id"        IS '操作人会员ID';
COMMENT ON COLUMN "douyin_verify_log"."verify_record_id" IS '关联核销记录 douyin_verify_record.id';
COMMENT ON COLUMN "douyin_verify_log"."poi_id"           IS '核销门店';
COMMENT ON COLUMN "douyin_verify_log"."origin_code"      IS '券码快照';
COMMENT ON COLUMN "douyin_verify_log"."result"           IS '操作结果 0成功 1失败';
COMMENT ON COLUMN "douyin_verify_log"."error_msg"        IS '失败描述';
COMMENT ON COLUMN "douyin_verify_log"."raw_request"      IS '请求入参快照(JSON)';
COMMENT ON COLUMN "douyin_verify_log"."raw_response"     IS '抖音响应原文快照';
COMMENT ON COLUMN "douyin_verify_log"."ip"               IS '请求IP';
COMMENT ON COLUMN "douyin_verify_log"."cost_ms"          IS '耗时(毫秒)';
COMMENT ON COLUMN "douyin_verify_log"."isdeleted"        IS '是否已删除 0未删除 1已删除';


-- ------------------------------------------------------------
-- 2026-06-25  v3.0.1  抖音核销开套餐 —— 核销记录加套餐卡ID列
-- 作者: rk
-- 说明: 核销成功开通 discount_member 后,回填套餐卡ID到核销记录,便于追溯。
-- 注意: goodsorder.payWay 新增取值 2=抖音券核销,前端支付方式展示需配合(若存在字典表则同步新增)。
-- ------------------------------------------------------------

ALTER TABLE "douyin_verify_record" ADD COLUMN IF NOT EXISTS "discount_member_id" varchar(64);
COMMENT ON COLUMN "douyin_verify_record"."discount_member_id" IS '核销成功开通的套餐卡ID(discount_member.id)';


-- ------------------------------------------------------------
-- 2026-06-25  v3.0.1  抖音核销配置入字典 —— 改后台维护、免重启
-- 作者: rk
-- 说明: client_key / client_secret / account_id / poi_id 从 yml 迁到字典
--       (system_dict + system_dict_data);门店ID等变动时,后台 /system/dictData 改即可,无需重启。
-- 执行前:把 4 处「待填」替换为真实值(client_secret 勿提交真值到仓库)。
-- 环境:PostgreSQL 13+ 内置 gen_random_uuid();低版本需先 CREATE EXTENSION pgcrypto。
-- 创建人/更新人:固定 system_user.id = 722ecd1e-e903-45b1-a839-c591c0af0d7e。
-- ------------------------------------------------------------

-- 父:抖音核销配置字典(已存在则跳过,幂等)
INSERT INTO system_dict (code, name, remark, create_user, update_user, create_time, update_time, deleted)
SELECT 'DOUYIN_CONFIG', '抖音核销配置',
       '抖音生活服务团购核销配置(client_key/client_secret/account_id/poi_id)',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
WHERE NOT EXISTS (SELECT 1 FROM system_dict WHERE code = 'DOUYIN_CONFIG' AND deleted = 0);

-- 子:四项配置(label 全大写,与 Java 常量 Constants.DOUYIN_* 对应;code 字段存「值」)
INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
SELECT gen_random_uuid()::text, d.id, '待填', 'CLIENT_KEY', 1, 0, '抖音应用 client_key',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
  AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'CLIENT_KEY' AND deleted = 0);

INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
SELECT gen_random_uuid()::text, d.id, '待填', 'CLIENT_SECRET', 2, 0, '抖音应用 client_secret',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
  AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'CLIENT_SECRET' AND deleted = 0);

INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
SELECT gen_random_uuid()::text, d.id, '待填', 'ACCOUNT_ID', 3, 0, '来客商户根账户ID',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
  AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'ACCOUNT_ID' AND deleted = 0);

INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
SELECT gen_random_uuid()::text, d.id, '待填', 'POI_ID', 4, 0, '核销门店ID(单门店)',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
FROM system_dict d WHERE d.code = 'DOUYIN_CONFIG' AND d.deleted = 0
  AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'POI_ID' AND deleted = 0);


-- ------------------------------------------------------------
-- 2026-06-25  v3.0.1  订单轨迹 —— 新增电车骑行轨迹表
-- 作者: rk
-- 说明: 仅电车(type=1,走 JT/T 808)骑行中产生实时轨迹;自行车(type=0)走 MQTT 无 GPS 上报,不产生。
--       写入点唯一:Jtt808Service.updateBikesInfo(0200 位置报文 for 循环内)。
--       订单关联经 Redis 缓存(ride:active:{bikeCode})获取,避免秒级上报高频查 member_rides。
--       关联字段说明:rides_id=骑行订单 member_rides.id;order_id=支付订单 member_rides.ordre_id→goodsorder.id(开锁时未绑定则为空)。
-- ------------------------------------------------------------

CREATE TABLE "member_rides_track" (
  "id"           varchar(64)   NOT NULL,
  "rides_id"     varchar(64),
  "order_id"     varchar(64),
  "bike_id"      varchar(64),
  "bike_code"    varchar(64),
  "longitude"    numeric(10,7),
  "latitude"     numeric(10,7),
  "report_time"  timestamp,
  "create_date"  timestamp     DEFAULT CURRENT_TIMESTAMP,
  "isdeleted"    smallint      DEFAULT 0,
  PRIMARY KEY ("id")
);
CREATE INDEX idx_mrt_rides_time ON "member_rides_track" ("rides_id", "report_time");
CREATE INDEX idx_mrt_order      ON "member_rides_track" ("order_id");
CREATE INDEX idx_mrt_bike       ON "member_rides_track" ("bike_id");
COMMENT ON TABLE  "member_rides_track" IS '电车骑行轨迹(JT/T 808 位置上报)';
COMMENT ON COLUMN "member_rides_track"."rides_id"    IS '骑行订单主键① member_rides.id';
COMMENT ON COLUMN "member_rides_track"."order_id"    IS '支付订单主键② member_rides.ordre_id → goodsorder.id(可能为空)';
COMMENT ON COLUMN "member_rides_track"."bike_id"     IS '车辆主键 bikes.id';
COMMENT ON COLUMN "member_rides_track"."bike_code"   IS '车辆编码 bikes.code';
COMMENT ON COLUMN "member_rides_track"."longitude"   IS '经度(高德 GCJ02,WGS84 转换后)';
COMMENT ON COLUMN "member_rides_track"."latitude"    IS '纬度(高德 GCJ02,WGS84 转换后)';
COMMENT ON COLUMN "member_rides_track"."report_time" IS '设备上报时间 deviceTime';
COMMENT ON COLUMN "member_rides_track"."isdeleted"   IS '是否已删除 0未删除 1已删除';


-- ------------------------------------------------------------
-- 2026-06-26  v3.0.1  小程序首页增加「抖音券兑换说明」字典项
-- 作者: rk
-- 说明: 归入既有 MINI_PROGRAMME 字典(与 STOP_SERVE_TIPS 同源);
--       小程序首页 /web/home/home 读取后下发,展示抖音券兑换规则说明。
--       label 全大写,与 Java 常量 Constants.DOUYIN_EXCHANGE_TIPS 对应;
--       code 字段存「值」(说明文案),执行前可先置默认文案。
-- 环境:PostgreSQL 13+ 内置 gen_random_uuid();低版本需先 CREATE EXTENSION pgcrypto。
-- ------------------------------------------------------------

INSERT INTO system_dict_data (id, dict_id, code, label, sort, disabled, info, create_user, update_user, create_time, update_time, deleted)
SELECT gen_random_uuid()::text, d.id,
       '抖音券兑换规则说明请咨询门店', 'DOUYIN_EXCHANGE_TIPS', 99, 0, '抖音券兑换说明(小程序首页展示)',
       '722ecd1e-e903-45b1-a839-c591c0af0d7e', '722ecd1e-e903-45b1-a839-c591c0af0d7e',
       CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0
FROM system_dict d WHERE d.code = 'MINI_PROGRAMME' AND d.deleted = 0
  AND NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_id = d.id AND label = 'DOUYIN_EXCHANGE_TIPS' AND deleted = 0);