-- =========================================================
-- Admin users (for admin panel login)
-- =========================================================

CREATE TABLE IF NOT EXISTS admin_users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(100) DEFAULT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default login created below:
--   username: admin
--   password: Admin@12345
-- CHANGE THIS PASSWORD after your first login (there's no
-- "change password" screen yet — update it directly in this
-- table with a new password_hash generated via PHP's
-- password_hash($newPassword, PASSWORD_BCRYPT), or ask me to
-- add a "change password" page).

INSERT INTO admin_users (username, password_hash, full_name)
VALUES ('admin', '$2b$10$/zk6hyMt7st8Fzo1iXLWyOJus9tX00Ye8zFYB93s17x33HlrJSlkG', 'Administrator')
ON DUPLICATE KEY UPDATE username = username;


-- =========================================================
-- training_certificates
-- Only run this if the table doesn't already exist on your
-- database — verify.php already expects this exact structure.
-- =========================================================

CREATE TABLE IF NOT EXISTS training_certificates (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    trainee_name      VARCHAR(150) NOT NULL,
    cpr_no            VARCHAR(50)  DEFAULT NULL,
    id_no             VARCHAR(50)  NOT NULL,
    certificate_no    VARCHAR(50)  NOT NULL,
    company_name      VARCHAR(150) DEFAULT NULL,
    certified_as      VARCHAR(150) DEFAULT NULL,
    issue_date        DATE DEFAULT NULL,
    expiry_date       DATE DEFAULT NULL,
    training_by       VARCHAR(150) DEFAULT NULL,
    status            VARCHAR(50)  DEFAULT 'Approved',
    active_status     VARCHAR(50)  DEFAULT NULL,
    certificate_file  VARCHAR(255) DEFAULT NULL,
    idcard_file       VARCHAR(255) DEFAULT NULL,
    wqt_certificate   VARCHAR(255) DEFAULT NULL,
    wqt_idcard        VARCHAR(255) DEFAULT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_certificate_no (certificate_no),
    KEY idx_id_no (id_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;