sql/schema.sql

-- Create Database
CREATE DATABASE IF NOT EXISTS sentiment_db;
USE sentiment_db;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('Admin', 'HOD', 'Staff', 'Student') NOT NULL DEFAULT 'Student',
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Feedback Table (Manual & Extracted)
CREATE TABLE IF NOT EXISTS feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    upload_id INT NULL,
    original_text TEXT NOT NULL,
    cleaned_text TEXT,
    sentiment ENUM('Positive', 'Negative', 'Neutral') NOT NULL,
    sentiment_score FLOAT NOT NULL,
    department_category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Uploads Table (Tracking Bulk Uploads)
CREATE TABLE IF NOT EXISTS uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    filename VARCHAR(255) NOT NULL,
    total_rows INT DEFAULT 0,
    processed_rows INT DEFAULT 0,
    status ENUM('Pending', 'Processing', 'Completed', 'Failed') DEFAULT 'Pending',
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Add foreign key back to feedback
ALTER TABLE feedback
ADD CONSTRAINT fk_upload_id
FOREIGN KEY (upload_id) REFERENCES uploads(id) ON DELETE CASCADE;

-- Insert default Admin User (password: admin123)
-- Hash generated by bcrypt in Python: bcrypt.hashpw(b'admin123', bcrypt.gensalt())
-- Note: Replace with actual hash below or let application handle first startup
INSERT INTO users (name, email, password_hash, role, department) 
VALUES ('System Admin', 'admin@example.com', '$2b$12$K8Z7J9vJ.5u9wD9/xS9T.O4a1xY7r/VjN6B2/5g3e4lq5vM1.bBki', 'Admin', 'Management')
ON DUPLICATE KEY UPDATE name='System Admin';