-- Create the Master Database
CREATE DATABASE IF NOT EXISTS tcsms_master;
USE tcsms_master;

-- Create the `users` Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Hashed password
    role ENUM('superadmin', 'center_admin', 'student') NOT NULL,
    center_id INT, -- NULL for superadmin
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create the `centers` Table
CREATE TABLE centers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL UNIQUE, -- Unique identifier for center
    status ENUM('pending', 'approved', 'suspended') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create the `modules` Table
CREATE TABLE modules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL, -- e.g., student, course, batch
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

-- Create the `center_modules` Table
CREATE TABLE center_modules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    center_id INT NOT NULL,
    module_id INT NOT NULL,
    custom_fields JSON, -- Store custom fields for this module
    FOREIGN KEY (center_id) REFERENCES centers(id),
    FOREIGN KEY (module_id) REFERENCES modules(id)
);

-- Create the `audit_logs` Table
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert Initial Data

-- Insert a Super Admin User
-- The password 'superadmin123' is hashed using PHP's password_hash() function.
-- Replace the hash below with a new hash if you want to use a different password.
-- Example PHP script to generate a hash: echo password_hash('superadmin123', PASSWORD_DEFAULT);
INSERT INTO users (username, password, role, email) 
VALUES ('superadmin', '$2y$10$6z6z6z6z6z6z6z6z6z6z6u6z6z6z6z6z6z6z6z6z6z6z6z6z6z6z6z6', 'superadmin', 'superadmin@example.com');

-- Insert Available Modules
INSERT INTO modules (name, description, is_active) VALUES
('student', 'Student Management Module', TRUE),
('course', 'Course Management Module', TRUE);