CREATE DATABASE memberdb;
USE memberdb;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255),
  role ENUM('admin','member') NOT NULL
);

CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  description TEXT
);

CREATE TABLE uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  task_id INT,
  filename VARCHAR(255),
  uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- Contoh data
INSERT INTO users (username, password, role) VALUES 
('admin', MD5('admin123'), 'admin'),
('member1', MD5('member123'), 'member');

INSERT INTO tasks (title, description) VALUES
('Tugas 1', 'Kerjakan soal matematika'),
('Tugas 2', 'Buat laporan praktikum');