36 lines
1.2 KiB
SQL
36 lines
1.2 KiB
SQL
-- Migration: Add tables for Teacher Collaboration Hub
|
|
CREATE TABLE IF NOT EXISTS resources (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
file_path VARCHAR(255),
|
|
teacher_id INT NOT NULL,
|
|
school_id INT NOT NULL,
|
|
grade VARCHAR(20),
|
|
subject VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS forum_posts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
author_id INT NOT NULL,
|
|
school_id INT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS forum_comments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
post_id INT NOT NULL,
|
|
author_id INT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|