-- MySQL Database Initialization Script

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    plain_password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    date VARCHAR(50),
    time VARCHAR(50),
    dueDate VARCHAR(50),
    status ENUM('todo', 'in_progress', 'completed') DEFAULT 'todo',
    completed BOOLEAN DEFAULT FALSE,
     summaries JSON DEFAULT '[]',
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS leaves (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    date VARCHAR(50) NOT NULL,
    reason VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
); 

-- Sample data for users
INSERT INTO users (name, email, phone, password) VALUES
('Alice Example', 'alice@example.com', '1234567890', '$2b$10$wH8QwQwQwQwQwQwQwQwQwOQwQwQwQwQwQwQwQwQwQwQwQwQwQW'); -- password: password123

-- Sample data for admins
INSERT INTO admins (email, password) VALUES
('admin@example.com', '$2b$10$wH8QwQwQwQwQwQwQwQwQwOQwQwQwQwQwQwQwQwQwQwQwQwQwQW'); -- password: password123

-- Sample data for tasks
INSERT INTO tasks (userId, title, date, time, dueDate, status, completed) VALUES
(1, 'Sample Task', '2024-06-01', '10:00', '2024-06-05', 'todo', false);

-- Sample data for leaves
INSERT INTO leaves (userId, name, date, reason, status) VALUES
(1, 'Alice Example', '2024-06-02', 'Medical', 'Pending');
