208 行
9.5 KiB
SQL
208 行
9.5 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
openid VARCHAR(64) NOT NULL,
|
|
nickname VARCHAR(100) NOT NULL DEFAULT '',
|
|
avatar VARCHAR(500) NOT NULL DEFAULT '',
|
|
phone VARCHAR(32) NOT NULL DEFAULT '',
|
|
gender TINYINT NOT NULL DEFAULT 0,
|
|
status TINYINT NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (user_id),
|
|
UNIQUE KEY uk_users_openid (openid)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
device_id VARCHAR(32) NOT NULL,
|
|
product_id VARCHAR(64) NOT NULL DEFAULT 'HOX_LIGHT_MASK',
|
|
device_secret VARCHAR(128) NOT NULL DEFAULT '',
|
|
device_name VARCHAR(100) NOT NULL DEFAULT '',
|
|
firmware_version VARCHAR(32) NOT NULL DEFAULT '1.0.0',
|
|
hardware_version VARCHAR(32) NOT NULL DEFAULT '',
|
|
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=inactive,2=online,3=offline,4=disabled',
|
|
battery TINYINT UNSIGNED NULL,
|
|
temperature TINYINT UNSIGNED NULL,
|
|
last_online_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (device_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS bindings (
|
|
binding_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
device_id VARCHAR(32) NOT NULL,
|
|
bind_token CHAR(16) NOT NULL DEFAULT '',
|
|
bind_expires DATETIME NULL,
|
|
bind_status TINYINT NOT NULL DEFAULT 1 COMMENT '1=active,2=inactive,3=pending',
|
|
bind_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
unbind_time DATETIME NULL,
|
|
PRIMARY KEY (binding_id),
|
|
KEY idx_bindings_user_status (user_id, bind_status),
|
|
KEY idx_bindings_device_status (device_id, bind_status),
|
|
CONSTRAINT fk_bindings_user FOREIGN KEY (user_id) REFERENCES users (user_id),
|
|
CONSTRAINT fk_bindings_device FOREIGN KEY (device_id) REFERENCES devices (device_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
subscription_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
plan VARCHAR(32) NOT NULL,
|
|
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=active,2=expired,3=cancelled',
|
|
amount DECIMAL(10,2) NOT NULL DEFAULT 0,
|
|
order_id VARCHAR(64) NOT NULL DEFAULT '',
|
|
start_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expire_time DATETIME NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (subscription_id),
|
|
KEY idx_subscriptions_user_status (user_id, status),
|
|
CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS treatment_records (
|
|
record_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
session_id VARCHAR(64) NOT NULL,
|
|
device_id VARCHAR(32) NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
start_time DATETIME NULL,
|
|
end_time DATETIME NULL,
|
|
regions VARCHAR(255) NOT NULL DEFAULT '',
|
|
total_duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
|
|
mode TINYINT NOT NULL DEFAULT 0,
|
|
avg_pd DECIMAL(8,4) NOT NULL DEFAULT 0,
|
|
battery TINYINT UNSIGNED NULL,
|
|
temperature TINYINT UNSIGNED NULL,
|
|
wavelength TINYINT UNSIGNED NULL,
|
|
brightness TINYINT UNSIGNED NULL,
|
|
pd_json JSON NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (record_id),
|
|
UNIQUE KEY uk_treatment_session (session_id),
|
|
KEY idx_treatment_user_created (user_id, created_at),
|
|
KEY idx_treatment_device_created (device_id, created_at),
|
|
CONSTRAINT fk_treatment_user FOREIGN KEY (user_id) REFERENCES users (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS scan_reports (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
device_id VARCHAR(32) NULL,
|
|
scanned_at DATETIME NULL,
|
|
regions JSON NULL COMMENT 'array of per-region analysis',
|
|
overall JSON NULL COMMENT 'array of overall tendencies',
|
|
recommend_mask INT NOT NULL DEFAULT 0 COMMENT 'bitmask of regions recommended for care',
|
|
recommend_plan JSON NULL COMMENT 'array of {wavelength, mask, label_key}',
|
|
raw_pd JSON NULL COMMENT 'raw PD samples for the calibration dataset (may be partial)',
|
|
calibrated TINYINT NOT NULL DEFAULT 0 COMMENT 'whether thresholds were calibrated',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
KEY idx_scan_reports_user_created (user_id, created_at),
|
|
KEY idx_scan_reports_device_created (device_id, created_at),
|
|
CONSTRAINT fk_scan_reports_user FOREIGN KEY (user_id) REFERENCES users (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS device_events (
|
|
event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
device_id VARCHAR(32) NOT NULL,
|
|
user_id BIGINT UNSIGNED NULL,
|
|
event_type VARCHAR(64) NOT NULL,
|
|
error_code INT NULL,
|
|
temperature TINYINT UNSIGNED NULL,
|
|
payload_json JSON NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (event_id),
|
|
KEY idx_device_events_device_created (device_id, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS device_commands (
|
|
command_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
device_id VARCHAR(32) NOT NULL,
|
|
admin_id BIGINT UNSIGNED NULL,
|
|
opcode TINYINT UNSIGNED NOT NULL,
|
|
payload_json JSON NULL,
|
|
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=pending,2=pulled,3=done,4=failed,5=cancelled',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
pulled_at DATETIME NULL,
|
|
finished_at DATETIME NULL,
|
|
result_json JSON NULL,
|
|
PRIMARY KEY (command_id),
|
|
KEY idx_device_commands_device_status (device_id, status, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS operation_logs (
|
|
log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NULL,
|
|
admin_id BIGINT UNSIGNED NULL,
|
|
action VARCHAR(64) NOT NULL,
|
|
detail VARCHAR(1000) NOT NULL DEFAULT '',
|
|
ip VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (log_id),
|
|
KEY idx_operation_logs_created (created_at),
|
|
KEY idx_operation_logs_action (action)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS admin_accounts (
|
|
admin_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NOT NULL,
|
|
password_hash VARCHAR(100) NOT NULL,
|
|
password_salt VARCHAR(32) NOT NULL DEFAULT '',
|
|
real_name VARCHAR(100) NOT NULL DEFAULT '',
|
|
role VARCHAR(32) NOT NULL DEFAULT 'admin',
|
|
status TINYINT NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (admin_id),
|
|
UNIQUE KEY uk_admin_username (username)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS system_settings (
|
|
setting_key VARCHAR(64) NOT NULL,
|
|
setting_value JSON NOT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (setting_key)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS firmware_files (
|
|
firmware_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
version VARCHAR(32) NOT NULL,
|
|
device_type VARCHAR(32) NOT NULL DEFAULT '',
|
|
cos_key VARCHAR(500) NOT NULL,
|
|
size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
|
|
sha256 CHAR(64) NOT NULL DEFAULT '',
|
|
status TINYINT NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (firmware_id),
|
|
KEY idx_firmware_version (version, status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS payment_orders (
|
|
order_id VARCHAR(64) NOT NULL COMMENT 'out_trade_no',
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
plan VARCHAR(32) NOT NULL,
|
|
amount_fen INT UNSIGNED NOT NULL DEFAULT 0,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'created' COMMENT 'created/paying/paid/closed/failed/refunded',
|
|
prepay_id VARCHAR(128) NOT NULL DEFAULT '',
|
|
transaction_id VARCHAR(64) NOT NULL DEFAULT '',
|
|
trade_state VARCHAR(32) NOT NULL DEFAULT '',
|
|
raw_notify_json JSON NULL,
|
|
paid_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (order_id),
|
|
KEY idx_payment_orders_user (user_id, status),
|
|
CONSTRAINT fk_payment_orders_user FOREIGN KEY (user_id) REFERENCES users (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES
|
|
('system_name', '"光子美容仪后台"'),
|
|
('monthly_price', '99'),
|
|
('yearly_price', '899'),
|
|
('trial_days', '7'),
|
|
('enable_register', 'true'),
|
|
('enable_binding', 'true'),
|
|
('enable_free_mode', 'true'),
|
|
('maintenance_mode', 'false'),
|
|
('diagnosis_config', '{"calibrated": false, "note": "阈值为待标定初始值(文档建议值)；polarity=inverted 与 pd_region_map(左PD1/中上PD2/中PD3,6,7/中下PD4/右PD5) 均已由固件确认(2026-07-28)", "polarity": "inverted", "th": 0.10, "severity": {"low": 0.05, "mid": 0.15, "high": 0.30}, "brightness": 204, "pd_region_map": {"left": [0], "top": [1], "middle": [2, 5, 6], "bottom": [3], "right": [4]}, "problem_wavelength": {"aging": 2, "acne": 3, "pigment": 4, "deep": 1}}');
|