STHApplication

CREATE TABLE IF NOT EXISTS Shop (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL DEFAULT '示例寄卖行',
    address TEXT,
    phone TEXT,
    creditCode TEXT,
    legalPerson TEXT,
    monthlyRate REAL NOT NULL DEFAULT 0.4,
    comprehensiveFeeRate REAL NOT NULL DEFAULT 2.4,
    storageFeeRate REAL NOT NULL DEFAULT 0.5,
    penaltyRate REAL NOT NULL DEFAULT 0.1,
    defaultPeriodMonths INTEGER NOT NULL DEFAULT 3,
    nearExpireDays INTEGER NOT NULL DEFAULT 7,
    logo TEXT,
    remark TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE TABLE IF NOT EXISTS PawnItemTypeRate (
    id TEXT PRIMARY KEY,
    shopId TEXT NOT NULL,
    itemType TEXT NOT NULL,
    label TEXT NOT NULL,
    monthlyRate REAL NOT NULL DEFAULT 0.4,
    storageFeeRate REAL NOT NULL DEFAULT 0.5,
    storageFixedFee REAL NOT NULL DEFAULT 0,
    serviceFeeRate REAL NOT NULL DEFAULT 2.4,
    defaultPeriodMonths INTEGER NOT NULL DEFAULT 3,
    enabled INTEGER NOT NULL DEFAULT 1,
    sortOrder INTEGER NOT NULL DEFAULT 0,
    attributes TEXT,
    remark TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE UNIQUE INDEX IF NOT EXISTS idx_itemtype_unique ON PawnItemTypeRate(shopId, itemType);

CREATE INDEX IF NOT EXISTS idx_itemtype_shop ON PawnItemTypeRate(shopId);

CREATE TABLE IF NOT EXISTS User (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    phone TEXT,
    role TEXT NOT NULL DEFAULT '店员',
    baseSalary REAL NOT NULL DEFAULT 0,
    commission REAL NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    openid TEXT UNIQUE,
    nickname TEXT,
    avatar TEXT,
    identityType TEXT,
    province TEXT,
    city TEXT,
    storeName TEXT,
    verified INTEGER NOT NULL DEFAULT 0,
    accessToken TEXT,
    refreshToken TEXT,
    tokenExpiresAt TEXT,
    lastLoginAt TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_user_openid ON User(openid);

CREATE TABLE IF NOT EXISTS Customer (
    id TEXT PRIMARY KEY,
    code TEXT,
    name TEXT NOT NULL,
    gender TEXT,
    idCard TEXT,
    idCardImages TEXT,
    phone TEXT,
    address TEXT,
    type TEXT NOT NULL DEFAULT 'PERSON',
    creditLevel TEXT NOT NULL DEFAULT 'NORMAL',
    isBlacklist INTEGER NOT NULL DEFAULT 0,
    tags TEXT,
    remark TEXT,
    deletedAt TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_customer_phone ON Customer(phone);

CREATE INDEX IF NOT EXISTS idx_customer_name ON Customer(name);

CREATE INDEX IF NOT EXISTS idx_customer_deleted ON Customer(deletedAt);

CREATE TABLE IF NOT EXISTS PawnTicket (
    id TEXT PRIMARY KEY,
    ticketNo TEXT NOT NULL UNIQUE,
    customerId TEXT NOT NULL,
    customerName TEXT NOT NULL,
    customerPhone TEXT,
    itemName TEXT NOT NULL,
    itemType TEXT NOT NULL,
    appraisalPrice REAL NOT NULL,
    principal REAL NOT NULL,
    monthlyRate REAL NOT NULL,
    fixedInterest REAL,
    storageFeeRate REAL NOT NULL DEFAULT 0,
    storageFixedFee REAL NOT NULL DEFAULT 0,
    feeRate REAL NOT NULL,
    periodMonths INTEGER NOT NULL,
    pawnDate TEXT NOT NULL,
    dueDate TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'ACTIVE',
    operatorId TEXT,
    feeChargeMode TEXT DEFAULT 'PREPAID',
    contractImages TEXT,
    remark TEXT,
    deletedAt TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_ticket_customer ON PawnTicket(customerId);

CREATE INDEX IF NOT EXISTS idx_ticket_status ON PawnTicket(status);

CREATE INDEX IF NOT EXISTS idx_ticket_due ON PawnTicket(dueDate);

CREATE INDEX IF NOT EXISTS idx_ticket_deleted ON PawnTicket(deletedAt);

CREATE TABLE IF NOT EXISTS PawnItem (
    id TEXT PRIMARY KEY,
    ticketId TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT,
    weight REAL,
    spec TEXT,
    brand TEXT,
    serialNo TEXT,
    accessory TEXT,
    appraisalPrice REAL,
    extraAttributes TEXT,
    images TEXT,
    remark TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_pawnitem_ticket ON PawnItem(ticketId);

CREATE TABLE IF NOT EXISTS RedeemRecord (
    id TEXT PRIMARY KEY,
    ticketId TEXT NOT NULL,
    redeemDate TEXT NOT NULL,
    principal REAL NOT NULL,
    interest REAL NOT NULL,
    storageFee REAL NOT NULL DEFAULT 0,
    fee REAL NOT NULL,
    penalty REAL NOT NULL,
    paid REAL NOT NULL,
    remark TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_redeem_ticket ON RedeemRecord(ticketId);

CREATE TABLE IF NOT EXISTS RenewRecord (
    id TEXT PRIMARY KEY,
    ticketId TEXT NOT NULL,
    renewDate TEXT NOT NULL,
    oldDueDate TEXT NOT NULL,
    newDueDate TEXT NOT NULL,
    newPrincipal REAL NOT NULL,
    storageFee REAL NOT NULL DEFAULT 0,
    fee REAL NOT NULL,
    remark TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_renew_ticket ON RenewRecord(ticketId);

CREATE TABLE IF NOT EXISTS FinanceCategory (
    id TEXT PRIMARY KEY,
    shopId TEXT NOT NULL,
    type TEXT NOT NULL,
    code TEXT NOT NULL,
    label TEXT NOT NULL,
    sortOrder INTEGER NOT NULL DEFAULT 0,
    enabled INTEGER NOT NULL DEFAULT 1,
    remark TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE UNIQUE INDEX IF NOT EXISTS idx_category_unique ON FinanceCategory(shopId, type, code);

CREATE INDEX IF NOT EXISTS idx_category_shop_type ON FinanceCategory(shopId, type);

CREATE TABLE IF NOT EXISTS FinanceRecord (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL,
    category TEXT NOT NULL,
    amount REAL NOT NULL,
    ticketId TEXT,
    operatorId TEXT,
    occurDate TEXT NOT NULL,
    remark TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_finance_type ON FinanceRecord(type);

CREATE INDEX IF NOT EXISTS idx_finance_category ON FinanceRecord(category);

CREATE INDEX IF NOT EXISTS idx_finance_occur ON FinanceRecord(occurDate);

CREATE TABLE IF NOT EXISTS Reminder (
    id TEXT PRIMARY KEY,
    ticketId TEXT NOT NULL,
    remindDate TEXT NOT NULL,
    type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'UNREAD',
    message TEXT NOT NULL,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_reminder_ticket ON Reminder(ticketId);

CREATE INDEX IF NOT EXISTS idx_reminder_status ON Reminder(status);

CREATE INDEX IF NOT EXISTS idx_reminder_date ON Reminder(remindDate);

CREATE TABLE IF NOT EXISTS OperationLog (
    id TEXT PRIMARY KEY,
    operatorId TEXT,
    action TEXT NOT NULL,
    targetType TEXT NOT NULL,
    targetId TEXT,
    targetName TEXT,
    detail TEXT,
    ipAddress TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_log_action ON OperationLog(action);

CREATE INDEX IF NOT EXISTS idx_log_target ON OperationLog(targetType, targetId);

CREATE INDEX IF NOT EXISTS idx_log_operator ON OperationLog(operatorId);

CREATE INDEX IF NOT EXISTS idx_log_created ON OperationLog(createdAt);

CREATE TABLE IF NOT EXISTS PrintTemplate (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    content TEXT NOT NULL,
    builtin INTEGER NOT NULL DEFAULT 0,
    isDefault INTEGER NOT NULL DEFAULT 0,
    pageSize TEXT NOT NULL DEFAULT 'A5',
    orientation TEXT NOT NULL DEFAULT 'portrait',
    remark TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_template_type_builtin ON PrintTemplate(type, builtin);

CREATE INDEX IF NOT EXISTS idx_template_type_default ON PrintTemplate(type, isDefault);

CREATE TABLE IF NOT EXISTS RecycleOrder (
    id TEXT PRIMARY KEY,
    source TEXT NOT NULL DEFAULT 'RECYCLE',
    orderNo TEXT NOT NULL UNIQUE,
    customerId TEXT NOT NULL,
    customerName TEXT NOT NULL,
    customerPhone TEXT,
    itemName TEXT NOT NULL,
    itemType TEXT NOT NULL,
    weight REAL,
    quotedPrice REAL NOT NULL DEFAULT 0,
    amount REAL NOT NULL,
    payMethod TEXT NOT NULL DEFAULT 'CASH',
    dealDate TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'DONE',
    operatorId TEXT,
    remark TEXT,
    deletedAt TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_recycle_customer ON RecycleOrder(customerId);

CREATE INDEX IF NOT EXISTS idx_recycle_status ON RecycleOrder(status);

CREATE INDEX IF NOT EXISTS idx_recycle_deal ON RecycleOrder(dealDate);

CREATE INDEX IF NOT EXISTS idx_recycle_deleted ON RecycleOrder(deletedAt);

CREATE TABLE IF NOT EXISTS RecycleItem (
    id TEXT PRIMARY KEY,
    orderId TEXT NOT NULL,
    -- 库存来源：RECYCLE=回收开单（orderId 是回收单 id）
    --           DEAD=绝当转入（orderId 写空串占位：老库该列是 NOT NULL 且 SQLite 无法改可空，
    --                来源当单记在 ticketId 上）
    source TEXT DEFAULT 'RECYCLE',
    ticketId TEXT,
    name TEXT NOT NULL,
    type TEXT,
    weight REAL,
    spec TEXT,
    brand TEXT,
    serialNo TEXT,
    accessory TEXT,
    quotedPrice REAL,
    amount REAL,
    extraAttributes TEXT,
    status TEXT DEFAULT 'IN_STOCK',
    listedPrice REAL,
    soldAt TEXT,
    images TEXT,
    remark TEXT,
    createdAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_recycleitem_order ON RecycleItem(orderId);

CREATE INDEX IF NOT EXISTS idx_recycleitem_status ON RecycleItem(status);

CREATE TABLE IF NOT EXISTS SaleRecord (
    id TEXT PRIMARY KEY,
    saleNo TEXT NOT NULL UNIQUE,
    itemId TEXT NOT NULL,
    customerId TEXT,
    customerName TEXT,
    amount REAL NOT NULL,
    cost REAL NOT NULL DEFAULT 0,
    profit REAL NOT NULL DEFAULT 0,
    payMethod TEXT NOT NULL DEFAULT 'CASH',
    saleDate TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'DONE',
    operatorId TEXT,
    remark TEXT,
    deletedAt TEXT,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

CREATE INDEX IF NOT EXISTS idx_sale_item ON SaleRecord(itemId);

CREATE INDEX IF NOT EXISTS idx_sale_date ON SaleRecord(saleDate);

CREATE INDEX IF NOT EXISTS idx_sale_deleted ON SaleRecord(deletedAt);

CREATE TABLE IF NOT EXISTS BusinessOrder (
 id TEXT PRIMARY KEY, orderNo TEXT NOT NULL UNIQUE, kind TEXT NOT NULL,
 customerId TEXT NOT NULL, customerName TEXT NOT NULL, customerPhone TEXT,
 itemName TEXT NOT NULL, itemType TEXT NOT NULL DEFAULT 'OTHER', amount REAL NOT NULL,
 deposit REAL NOT NULL DEFAULT 0, commission REAL NOT NULL DEFAULT 0,
 startDate TEXT NOT NULL, dueDate TEXT, endDate TEXT, status TEXT NOT NULL DEFAULT 'OPEN',
 images TEXT, remark TEXT, deletedAt TEXT, createdAt TEXT NOT NULL, updatedAt TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_business_kind ON BusinessOrder(kind, status);
CREATE TABLE IF NOT EXISTS FundingAccount (
 id TEXT PRIMARY KEY, name TEXT NOT NULL, providerType TEXT NOT NULL,
 principal REAL NOT NULL, rate REAL NOT NULL, ratePeriod TEXT NOT NULL,
 dayBasis INTEGER NOT NULL, startDate TEXT NOT NULL, endDate TEXT, remark TEXT,
 deletedAt TEXT, trashGroup TEXT, createdAt TEXT NOT NULL, updatedAt TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS FundingSettlement (
 id TEXT PRIMARY KEY, accountId TEXT NOT NULL, name TEXT NOT NULL,
 periodStart TEXT NOT NULL, periodEnd TEXT NOT NULL, days INTEGER NOT NULL,
 amount REAL NOT NULL, occurDate TEXT NOT NULL, principal REAL NOT NULL,
 rate REAL NOT NULL, ratePeriod TEXT NOT NULL, dayBasis INTEGER NOT NULL,
 remark TEXT, deletedAt TEXT, trashGroup TEXT, createdAt TEXT NOT NULL,
 UNIQUE(accountId,periodEnd)
);
CREATE INDEX IF NOT EXISTS idx_funding_account ON FundingSettlement(accountId,periodEnd);
CREATE TABLE IF NOT EXISTS Advertisement (
 id TEXT PRIMARY KEY, title TEXT NOT NULL, subtitle TEXT, image TEXT, link TEXT,
 enabled INTEGER NOT NULL DEFAULT 1, sortOrder INTEGER NOT NULL DEFAULT 0,
 createdAt TEXT NOT NULL, updatedAt TEXT NOT NULL
);