Spaces:
Running
Running
File size: 1,968 Bytes
e45ddff | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | -- 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';
|