package com.example.util;

public class SqlQueries {
    // 查询
    public static final String GET_ALL_USERS = 
        "SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users ORDER BY id";
    
    public static final String GET_USER_BY_ID = 
        "SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users WHERE id = :id";
    
    public static final String GET_USER_BY_USERNAME = 
        "SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users WHERE username = :username";
    
    // 分页查询
    public static final String GET_USERS_PAGED = 
        "SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users ORDER BY id LIMIT :limit OFFSET :offset";
    
    // 高级分页查询（支持排序和条件筛选）
    public static final String GET_USERS_ADVANCED = 
        "SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users " +
        "WHERE 1=1 " +
        "#{if}(:username != '') AND username LIKE '%' || :username || '%'#{end} " +
        "#{if}(:email != '') AND email LIKE '%' || :email || '%'#{end} " +
        "#{if}(:phone != '') AND phone LIKE '%' || :phone || '%'#{end} " +
        "#{if}(:active != null) AND active = :active#{end} " +
        "#{if}(:fromDate != '') AND create_time >= TO_TIMESTAMP(:fromDate, 'YYYY-MM-DD')#{end} " +
        "#{if}(:toDate != '') AND create_time <= TO_TIMESTAMP(:toDate, 'YYYY-MM-DD') + INTERVAL '1 day'#{end} " +
        "ORDER BY " +
        "#{if}(:sortField = 'username') username#{end} " +
        "#{if}(:sortField = 'email') email#{end} " +
        "#{if}(:sortField = 'createTime') create_time#{end} " +
        "#{if}(:sortField = '') id#{end} " +
        "#{if}(:sortOrder = 'ASC') ASC#{else} DESC#{end} " +
        "LIMIT :limit OFFSET :offset";
    
    public static final String COUNT_USERS_ADVANCED = 
        "SELECT COUNT(*) as total FROM users " +
        "WHERE 1=1 " +
        "#{if}(:username != '') AND username LIKE '%' || :username || '%'#{end} " +
        "#{if}(:email != '') AND email LIKE '%' || :email || '%'#{end} " +
        "#{if}(:phone != '') AND phone LIKE '%' || :phone || '%'#{end} " +
        "#{if}(:active != null) AND active = :active#{end} " +
        "#{if}(:fromDate != '') AND create_time >= TO_TIMESTAMP(:fromDate, 'YYYY-MM-DD')#{end} " +
        "#{if}(:toDate != '') AND create_time <= TO_TIMESTAMP(:toDate, 'YYYY-MM-DD') + INTERVAL '1 day'#{end}";
    
    public static final String COUNT_USERS = 
        "SELECT COUNT(*) as total FROM users";
    
    // 插入
    public static final String CREATE_USER = 
        "INSERT INTO users (username, email, password, full_name, phone, active, create_time, update_time) " +
        "VALUES (:username, :email, :password, :fullName, :phone, :active, NOW(), NOW()) RETURNING id";
    
    // 更新
    public static final String UPDATE_USER = 
        "UPDATE users SET username = :username, email = :email, " +
        "password = CASE WHEN :password = '' THEN password ELSE :password END, " +
        "full_name = :fullName, phone = :phone, active = :active, update_time = NOW() " +
        "WHERE id = :id";
    
    // 删除
    public static final String DELETE_USER = 
        "DELETE FROM users WHERE id = :id";
    
    // 创建用户表 - 用于初始化数据库
    public static final String CREATE_USERS_TABLE = 
        "CREATE TABLE IF NOT EXISTS users (" +
        "id SERIAL PRIMARY KEY, " +
        "username VARCHAR(50) NOT NULL UNIQUE, " +
        "email VARCHAR(100) NOT NULL UNIQUE, " +
        "password VARCHAR(255) NOT NULL, " +
        "full_name VARCHAR(100), " +
        "phone VARCHAR(20), " +
        "active BOOLEAN DEFAULT TRUE, " +
        "create_time TIMESTAMP NOT NULL, " +
        "update_time TIMESTAMP NOT NULL" +
        ")";
}