-- ================================================
-- SQL Script for Admin & SubAdmin Roles
-- ================================================

-- 1. Create SuperAdmins Table
CREATE TABLE Admins (
    admin_id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
);

-- Insert a default SuperAdmin
INSERT INTO Admins (username, password) 
VALUES ('superadmin', 'admin123'); -- Change password as needed

-- 2. Create SubAdmins Table for Role-Based Access
CREATE TABLE SubAdmins (
    sub_admin_id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(255) NULL, -- Can be comma-separated list like 'users,posts,complaints' or NULL for pending
    created_at DATETIME DEFAULT GETDATE()
);

-- ================================================
-- 3. Staff Tables (staff signup + task assignment)
-- ================================================

CREATE TABLE Staffs (
    staff_id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    assigned_roles VARCHAR(255) NULL, -- comma-separated: users,posts,complaints
    created_at DATETIME DEFAULT GETDATE()
);

CREATE TABLE StaffNotifications (
    notification_id INT IDENTITY(1,1) PRIMARY KEY,
    staff_id INT NOT NULL,
    message VARCHAR(500) NOT NULL,
    is_read BIT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT GETDATE()
);

-- ================================================
-- 4. Complaints Upgrades (name, status, attachments, admin/student messages)
-- ================================================

-- Add new columns if missing
IF COL_LENGTH('Complaints', 'student_name') IS NULL
    ALTER TABLE Complaints ADD student_name VARCHAR(100) NULL;

IF COL_LENGTH('Complaints', 'user_id') IS NULL
    ALTER TABLE Complaints ADD user_id INT NULL;

IF COL_LENGTH('Complaints', 'status') IS NULL
    ALTER TABLE Complaints ADD status VARCHAR(30) NULL CONSTRAINT DF_Complaints_Status DEFAULT('Submitted');

IF COL_LENGTH('Complaints', 'submitted_at') IS NULL
    ALTER TABLE Complaints ADD submitted_at DATETIME NULL CONSTRAINT DF_Complaints_SubmittedAt DEFAULT(GETDATE());

-- Attachments table
IF OBJECT_ID('dbo.ComplaintAttachments', 'U') IS NULL
BEGIN
    CREATE TABLE ComplaintAttachments (
        attachment_id INT IDENTITY(1,1) PRIMARY KEY,
        complaint_id INT NOT NULL,
        file_url VARCHAR(400) NOT NULL,
        mime_type VARCHAR(100) NULL,
        created_at DATETIME DEFAULT GETDATE()
    );
END

-- Message thread table (Admin <-> Student)
IF OBJECT_ID('dbo.ComplaintMessages', 'U') IS NULL
BEGIN
    CREATE TABLE ComplaintMessages (
        message_id INT IDENTITY(1,1) PRIMARY KEY,
        complaint_id INT NOT NULL,
        sender VARCHAR(20) NOT NULL, -- 'Admin' or 'Student'
        message VARCHAR(1000) NOT NULL,
        is_read_by_student BIT NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT GETDATE()
    );
END

-- Insert some dummy SubAdmins for testing (Optional)
-- INSERT INTO SubAdmins (username, email, password, role) VALUES ('subadmin_users', 'users@admin.com', '123', 'users');
-- INSERT INTO SubAdmins (username, email, password, role) VALUES ('subadmin_posts', 'posts@admin.com', '123', 'posts');
-- INSERT INTO SubAdmins (username, email, password, role) VALUES ('subadmin_comp', 'comp@admin.com', '123', 'complaints');