database.sql

-- =====================================================
-- Database: chemistry_lab
-- Compatible with existing PHP submission code
-- =====================================================

DROP DATABASE IF EXISTS chemistry_lab;
CREATE DATABASE chemistry_lab 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

USE chemistry_lab;

-- =====================================================
-- USERS TABLE
-- =====================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('student','professor') NOT NULL
);

-- =====================================================
-- EXPERIMENTS TABLE
-- =====================================================
CREATE TABLE experiments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    difficulty VARCHAR(20) DEFAULT 'medium'
);

-- =====================================================
-- SUBMISSIONS TABLE
-- =====================================================
CREATE TABLE submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    experiment_id INT NOT NULL,
    experiment_name VARCHAR(100) NOT NULL,
    video_path VARCHAR(255) DEFAULT NULL,
    steps TEXT NOT NULL,
    grade INT DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_submissions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_submissions_experiment
        FOREIGN KEY (experiment_id) REFERENCES experiments(id)
        ON DELETE CASCADE
);

-- =====================================================
-- SAMPLE DATA (optional but useful)
-- =====================================================

INSERT INTO users (email, username, password, role) VALUES
('student@test.com', 'aya', '123456', 'student'),
('prof@test.com', 'ara', '654321', 'professor');

INSERT INTO experiments (name, difficulty) VALUES
('Color Change Reaction', 'easy'),
('Gas Production Experiment', 'medium'),
('Acid Base Titration', 'hard');