-- 创建卡钱包主表
-- 用于管理物联网卡和设备级别的钱包

CREATE TABLE IF NOT EXISTS tb_card_wallet (
    id BIGSERIAL PRIMARY KEY,
    resource_type VARCHAR(20) NOT NULL,
    resource_id BIGINT NOT NULL,
    balance BIGINT NOT NULL DEFAULT 0,
    frozen_balance BIGINT NOT NULL DEFAULT 0,
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
    status INT NOT NULL DEFAULT 1,
    version INT NOT NULL DEFAULT 0,
    shop_id_tag BIGINT NOT NULL,
    enterprise_id_tag BIGINT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,

    -- 约束
    CONSTRAINT chk_card_wallet_balance CHECK (balance >= 0),
    CONSTRAINT chk_card_wallet_frozen_balance CHECK (frozen_balance >= 0 AND frozen_balance <= balance),
    CONSTRAINT chk_card_wallet_status CHECK (status IN (1, 2, 3)),
    CONSTRAINT chk_card_wallet_type CHECK (resource_type IN ('iot_card', 'device'))
);

-- 唯一索引：resource_type + resource_id 在未删除时唯一
CREATE UNIQUE INDEX idx_card_wallet_resource ON tb_card_wallet (resource_type, resource_id) WHERE deleted_at IS NULL;

-- 状态索引
CREATE INDEX idx_card_wallet_status ON tb_card_wallet (status);

-- 多租户过滤索引
CREATE INDEX idx_card_wallet_shop_tag ON tb_card_wallet (shop_id_tag);

-- 企业标签索引
CREATE INDEX idx_card_wallet_enterprise_tag ON tb_card_wallet (enterprise_id_tag) WHERE enterprise_id_tag IS NOT NULL;

-- 添加注释
COMMENT ON TABLE tb_card_wallet IS '卡钱包主表';
COMMENT ON COLUMN tb_card_wallet.resource_type IS '资源类型：iot_card-物联网卡 | device-设备';
COMMENT ON COLUMN tb_card_wallet.resource_id IS '资源 ID（关联 tb_iot_card.id 或 tb_device.id）';
COMMENT ON COLUMN tb_card_wallet.balance IS '余额（单位：分）';
COMMENT ON COLUMN tb_card_wallet.frozen_balance IS '冻结余额（单位：分）';
COMMENT ON COLUMN tb_card_wallet.currency IS '币种';
COMMENT ON COLUMN tb_card_wallet.status IS '钱包状态：1-正常 2-冻结 3-关闭';
COMMENT ON COLUMN tb_card_wallet.version IS '版本号（乐观锁）';
COMMENT ON COLUMN tb_card_wallet.shop_id_tag IS '店铺 ID 标签（多租户过滤）';
COMMENT ON COLUMN tb_card_wallet.enterprise_id_tag IS '企业 ID 标签（多租户过滤）';