-- สำหรับฐานข้อมูล HR (ตัวอย่าง)
CREATE DATABASE IF NOT EXISTS hr_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hr_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- ควรเป็น hash ของรหัสผ่าน (เช่น password_hash)
    name VARCHAR(100) NOT NULL
);

-- สร้างผู้ใช้เริ่มต้น (รหัสผ่านคือ 'password' hash ด้วย bcrypt)
INSERT IGNORE INTO users (username, password, name) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ผู้ดูแลระบบ');

-- สำหรับฐานข้อมูล hosxp_export_person (ประวัติการทำงาน)
CREATE DATABASE IF NOT EXISTS hosxp_export_person DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hosxp_export_person;

CREATE TABLE IF NOT EXISTS system_admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cid VARCHAR(13) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS general_settings (
    setting_key VARCHAR(50) PRIMARY KEY,
    setting_value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS export_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_name VARCHAR(100) NOT NULL,
    excel_columns VARCHAR(255) NOT NULL, -- e.g. "pid, hn"
    sql_query TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS export_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    profile_id INT NULL,
    file_name VARCHAR(255) NOT NULL,
    total_rows INT NOT NULL,
    found_rows INT NOT NULL,
    upload_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profile_id) REFERENCES export_profiles(id) ON DELETE SET NULL
);

-- เพิ่มข้อมูลตัวอย่าง
INSERT IGNORE INTO export_profiles (id, profile_name, excel_columns, sql_query) VALUES (
    1, 
    'ค้นหาด้วย PID เบื้องต้น', 
    'pid', 
    'SELECT p.person_id, p.patient_hn, p.cid, concat(p.pname, p.fname, '' '', p.lname) as nme, pt.birthday, pt.hometel, pt.mobile_phone_number FROM person p LEFT OUTER JOIN patient pt ON p.patient_hn = pt.hn WHERE p.person_id = :pid'
);