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

-- 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 Available Modules
INSERT INTO modules (name, description, is_active) VALUES
('student', 'Student Management Module', TRUE),
('course', 'Course Management Module', TRUE);