/**
 * SQL语句定义文件
 * 包含创建表的SQL语句和其他常用SQL查询
 */

/**
 * id:主键
 * username:用户名
 * password:密码
 * real_name:真实姓名
 * role:角色
 * last_login_time:上次登录事件
 */
// 创建用户表
const createUserTable = `
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    real_name TEXT,
    role TEXT DEFAULT 'user',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_time TIMESTAMP
)`;

/**
 * id:主键
 * orderNo:订单号
 * status:状态
 * originalDoc:原始单据
 * customer:客户
 * createUserId:创建人id
 * createTime:创建时间
 * completeTime:完成时间
 * remark:备注
 */
// 创建订单表
const createOrderTable = `
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    orderNo TEXT NOT NULL UNIQUE,
    status TEXT DEFAULT 'pedding',
    originalDoc TEXT,
	customer TEXT,
    createUserId INTEGER,
    createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completeTime TIMESTAMP,
    remark TEXT
)`;

/**
 * id:主键
 * name:物料名称
 * create_time:创建时间
 */
// 创建核心物料表
const createCoreMaterialTable = `
CREATE TABLE IF NOT EXISTS core_materials (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

/**
 * id:主键
 * orderNo:订单号
 * material_name:物料名称
 * tab_order:tab排序
 */
// 创建订单物料关联表
const createOrderMaterialTable = `
CREATE TABLE IF NOT EXISTS order_materials (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    orderNo TEXT NOT NULL,
    material_name TEXT NOT NULL,
    tab_order INTEGER
)`;

/**
 * id:主键
 * orderNo:订单号
 * material_name:物料名称
 * reference_content:标定信息
 * reference_content_index:标定信息索引
 * original_content:二维码原始信息
 */
//物料标定信息表
const createMaterialContentTable = `
CREATE TABLE IF NOT EXISTS materials_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    orderNo TEXT NOT NULL,
    material_name TEXT NOT NULL,
    reference_content TEXT,
	reference_content_index TEXT,
	original_content TEXT
)`;

/**
 * id:主键
 * orderNo:订单号
 * materialName:物料名称
 * scan_content:扫描信息
 * reference_content:标定信息
 * is_matched:是否匹配
 * scan_time:扫描时间
 */
// 创建扫描记录表
const createScanRecordTable = `
CREATE TABLE IF NOT EXISTS scan_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    orderNo TEXT NOT NULL,
    materialName TEXT NOT NULL,
    scan_content TEXT NOT NULL,
    reference_content TEXT,
    is_matched BOOLEAN,
    scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

// 创建激活信息表(暂时无用)
const createActivationTable = `
CREATE TABLE IF NOT EXISTS activation_info (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_uuid TEXT NOT NULL UNIQUE,
    device_info TEXT,
    runtime_code TEXT,
    activation_code TEXT,
    is_activated BOOLEAN DEFAULT 0,
    trial_start_date DATE,
    trial_end_date DATE,
    activation_date DATE,
    expiration_date DATE
)`;

// 创建系统设置表(暂时无用)
const createSettingsTable = `
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT NOT NULL UNIQUE,
    setting_value TEXT,
    setting_desc TEXT,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

/**
 * id:主键
 * seq:序号
 * date:时间
 */
//序列表
const createSeqTable = `
CREATE TABLE IF NOT EXISTS seq (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	seq INTEGER NOT NULL,
	date TEXT NOT NULL
)`;

/**
 * id:主键
 * customerName:客户名称
 * create_time:创建时间
 */
//客户表
const createCustomerTable = `
CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	customerName TEXT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

// 初始化系统设置
const initSettings = `
INSERT OR IGNORE INTO settings (setting_key, setting_value, setting_desc) VALUES
('retention_days', '90', '订单数据保留天数')
`;

// 初始化管理员账户
const initAdminUser = `
INSERT OR IGNORE INTO users (username, password, real_name, role) VALUES
('admin', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '系统管理员', 'admin')
`;

const initSeq = `
INSERT OR IGNORE INTO seq (seq, date) VALUES (1, CURRENT_DATE)
`

// 导出所有SQL语句
export const createTables = [
	createUserTable,
	createOrderTable,
	createCoreMaterialTable,
	createOrderMaterialTable,
	createScanRecordTable,
	createActivationTable,
	createSettingsTable,
	createMaterialContentTable,
	createSeqTable,
	createCustomerTable
];

// 导出初始化数据的SQL语句
export const initData = [
	initSettings,
	initAdminUser,
	initSeq
];

// 常用查询语句
export const queries = {
	// 用户相关
	getUserByUsername: 'SELECT * FROM users WHERE username = ?',
	updateUserLastLogin: 'UPDATE users SET last_login_time = CURRENT_TIMESTAMP WHERE id = ?',

	// 订单相关
	createOrder: 'INSERT INTO orders (order_no, order_name, create_user_id) VALUES (?, ?, ?)',
	updateOrderStatus: 'UPDATE orders SET order_status = ? WHERE id = ?',
	getOrderById: 'SELECT * FROM orders WHERE id = ?',
	getOrderByNo: 'SELECT * FROM orders WHERE order_no = ?',
	getRecentOrders: 'SELECT * FROM orders ORDER BY create_time DESC LIMIT ?',

	// 核心物料相关
	getAllMaterials: 'SELECT * FROM core_materials ORDER BY last_used_time DESC',
	getMaterialById: 'SELECT * FROM core_materials WHERE id = ?',
	getMaterialByCode: 'SELECT * FROM core_materials WHERE material_code = ?',
	updateMaterialLastUsed: 'UPDATE core_materials SET last_used_time = CURRENT_TIMESTAMP WHERE id = ?',

	// 扫描记录相关
	addScanRecord: 'INSERT INTO scan_records (order_id, material_id, scan_content, reference_content, is_matched, scan_user_id) VALUES (?, ?, ?, ?, ?, ?)',
	getScanRecordsByOrderId: 'SELECT * FROM scan_records WHERE order_id = ? ORDER BY scan_time DESC',

	// 激活相关
	getActivationInfo: 'SELECT * FROM activation_info WHERE device_uuid = ?',
	updateActivationCode: 'UPDATE activation_info SET activation_code = ?, is_activated = 1, activation_date = CURRENT_DATE, expiration_date = ? WHERE device_uuid = ?'
};