44 lines
1.4 KiB
SQL
44 lines
1.4 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
openid TEXT NOT NULL UNIQUE,
|
|
unionid TEXT,
|
|
phone TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_login_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_phone_unique_idx
|
|
ON users(phone)
|
|
WHERE phone IS NOT NULL AND phone <> '';
|
|
|
|
-- Retained for one-time price-inquiry migration markers.
|
|
CREATE TABLE IF NOT EXISTS app_config (
|
|
config_key TEXT PRIMARY KEY,
|
|
config_value TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS price_inquiries (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT REFERENCES users(id),
|
|
phone TEXT NOT NULL,
|
|
province TEXT NOT NULL,
|
|
city TEXT NOT NULL,
|
|
district TEXT NOT NULL,
|
|
project_name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
remark TEXT NOT NULL DEFAULT '',
|
|
last_submitted_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'now', '+8 hours')),
|
|
created_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'now', '+8 hours')),
|
|
updated_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'now', '+8 hours'))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS price_inquiries_phone_unique_idx
|
|
ON price_inquiries(phone);
|
|
|
|
CREATE INDEX IF NOT EXISTS price_inquiries_region_idx
|
|
ON price_inquiries(province, city, district, created_at DESC);
|