-- Database Schema for News Registration System

CREATE DATABASE IF NOT EXISTS news_registration;
USE news_registration;

-- Departments table
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- Statuses table
CREATE TABLE statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- Insert default statuses
INSERT INTO statuses (name) VALUES ('รอตรวจสอบ'), ('แก้ไข'), ('เผยแพร่แล้ว');

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'approver', 'user') NOT NULL,
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

-- News table
CREATE TABLE news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT NOT NULL,
    user_id INT NOT NULL,
    number VARCHAR(50) NOT NULL, -- e.g., ฉ.1/2568
    date DATE NOT NULL,
    content TEXT NOT NULL,
    status_id INT NOT NULL DEFAULT 1, -- default to 'รอตรวจสอบ'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (status_id) REFERENCES statuses(id)
);

-- Insert sample departments
INSERT INTO departments (name) VALUES ('หน่วยงาน A'), ('หน่วยงาน B');