CREATE DATABASE IF NOT EXISTS company_dashboard DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE company_dashboard;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    pin_hash VARCHAR(255) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    cnic_front VARCHAR(255) NOT NULL,
    cnic_back VARCHAR(255) NOT NULL,
    profile_pic VARCHAR(255) NOT NULL,
    role ENUM('Owner', 'Admin', 'User') DEFAULT 'User',
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE features (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feature_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_permissions (
    user_id INT,
    feature_id INT,
    PRIMARY KEY(user_id, feature_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE
);

CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date DATE NOT NULL,
    status ENUM('Present', 'Absent', 'Holiday') DEFAULT 'Present',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, date)
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Default Master Owner
-- Email: owner@company.com | Password: Admin@123 | PIN: 1234
INSERT INTO users (full_name, phone, email, password_hash, pin_hash, gender, cnic_front, cnic_back, profile_pic, role, status) 
VALUES ('Master Owner', '0000000000', 'owner@company.com', '$2y$10$wS2C7Z8N2iB2lZ.R9kU5.O1jE1Y5rA1iF2cR5oJ8mN9kU5.O1jE1Y', '$2y$10$yX3D8A9O3jC3mZ.S0lV6.P2kF2Z6sB2jG3dS6pK9nO0lV6.P2kF2Z', 'Male', 'none', 'none', 'none', 'Owner', 'Approved');

-- Default Features
INSERT INTO features (feature_name) VALUES ('Donation Portal'), ('Marketing Assets'), ('Financial Reports');