CREATE DATABASE IF NOT EXISTS learning_platform DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE learning_platform;

-- Admin Users
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('admin', 'staff') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Learning Resources (Directory)
CREATE TABLE IF NOT EXISTS learning_resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    knowledge_detail TEXT,
    subject_id INT,
    type_id INT,
    wisdom_id INT,
    sub_district VARCHAR(100),
    district VARCHAR(100),
    province VARCHAR(100),
    phone VARCHAR(20),
    image_url VARCHAR(255),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Knowledge Bank Content
CREATE TABLE IF NOT EXISTS knowledge_bank (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category ENUM('article', 'activity', 'video', 'publication') NOT NULL,
    content TEXT,
    media_url VARCHAR(255),
    tags VARCHAR(255),
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Featured Activities
CREATE TABLE IF NOT EXISTS featured_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    poster_url VARCHAR(255),
    activity_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System Settings & About Us
CREATE TABLE IF NOT EXISTS system_settings (
    id INT PRIMARY KEY,
    platform_name VARCHAR(255) DEFAULT 'แพลตฟอร์มแหล่งเรียนรู้',
    about_background TEXT,
    about_types TEXT,
    about_wisdom TEXT,
    about_references TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initial Admin User (password: admin123)
INSERT INTO users (username, password, full_name) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator');

-- Initial Settings
INSERT INTO system_settings (id, platform_name) VALUES (1, 'แพลตฟอร์มแหล่งเรียนรู้');