-- Database: `projects_membershipdb`
CREATE DATABASE IF NOT EXISTS `projects_membershipdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `projects_membershipdb`;

-- Users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'editor', 'viewer') NOT NULL DEFAULT 'viewer',
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `avatar` VARCHAR(255) DEFAULT NULL,
    `last_login` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Password resets table
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `used` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings table
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(255) NOT NULL UNIQUE,
    `value` TEXT,
    `type` ENUM('text', 'textarea', 'email', 'number', 'select', 'boolean') NOT NULL DEFAULT 'text',
    `group` VARCHAR(100) DEFAULT 'general',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit logs table
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `action` VARCHAR(255) NOT NULL,
    `entity_type` VARCHAR(100) DEFAULT NULL,
    `entity_id` INT DEFAULT NULL,
    `description` TEXT,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Calendar events table
CREATE TABLE IF NOT EXISTS `calendar_events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME DEFAULT NULL,
    `color` VARCHAR(20) DEFAULT '#4e73df',
    `status` ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Insert demo users (password: password123)
INSERT INTO `users` (`name`, `email`, `password`, `role`, `status`, `approval_status`) VALUES
('Admin User', 'admin@example.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'admin', 'active', 'approved'),
('John Editor', 'editor@example.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'editor', 'active', 'approved'),
('Jane Viewer', 'viewer@example.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'viewer', 'active', 'approved'),
('Sarah Wilson', 'sarah@example.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'editor', 'active', 'approved'),
('Mike Johnson', 'mike@example.com', '$2y$10$rmFmoEPCudgV7BfUpoJje.3uZUOEacpIg8igWkHaCV29Vgu.tzotS', 'viewer', 'inactive', 'approved');

-- Insert demo settings
INSERT INTO `settings` (`key`, `value`, `type`, `group`) VALUES
('app_name', 'AdminSB Pro', 'text', 'general'),
('app_description', 'Powerful Admin Dashboard', 'textarea', 'general'),
('admin_email', 'admin@example.com', 'email', 'general'),
('items_per_page', '10', 'number', 'general'),
('maintenance_mode', 'false', 'boolean', 'general'),
('timezone', 'UTC', 'select', 'regional'),
('date_format', 'Y-m-d', 'select', 'regional'),
('enable_registration', 'true', 'boolean', 'general'),
('theme_color', '#4e73df', 'text', 'appearance'),
('sidebar_collapsed', 'false', 'boolean', 'appearance'),
('company_name', 'AdminSB Pro', 'text', 'general'),
('company_logo', '', 'file', 'general'),
('company_favicon', '', 'file', 'general');

-- Insert demo audit logs
INSERT INTO `audit_logs` (`user_id`, `action`, `entity_type`, `entity_id`, `description`, `ip_address`) VALUES
(1, 'user.login', 'session', 1, 'Admin User logged in', '127.0.0.1'),
(1, 'user.create', 'users', 4, 'Created user Sarah Wilson', '127.0.0.1'),
(1, 'user.update', 'users', 3, 'Updated role for Jane Viewer', '127.0.0.1'),
(2, 'user.login', 'session', 2, 'John Editor logged in', '127.0.0.1'),
(1, 'settings.update', 'settings', 1, 'Updated application name', '127.0.0.1'),
(1, 'user.delete', 'users', 5, 'Deleted user Mike Johnson', '127.0.0.1'),
(2, 'event.create', 'calendar_events', 1, 'Created event Team Meeting', '127.0.0.1'),
(1, 'password.reset', 'users', 2, 'Password reset for John Editor', '127.0.0.1'),
(3, 'user.login', 'session', 3, 'Jane Viewer logged in', '127.0.0.1'),
(1, 'settings.update', 'settings', 5, 'Changed maintenance mode', '127.0.0.1');

-- Insert demo calendar events
INSERT INTO `calendar_events` (`user_id`, `title`, `description`, `start_date`, `end_date`, `color`, `status`) VALUES
(1, 'Team Meeting', 'Weekly team sync meeting', '2026-06-01 09:00:00', '2026-06-01 10:00:00', '#4e73df', 'pending'),
(1, 'Project Deadline', 'Q2 project submission deadline', '2026-06-15 17:00:00', '2026-06-15 18:00:00', '#e74a3b', 'pending'),
(2, 'Client Call', 'Call with ABC Corp', '2026-06-03 14:00:00', '2026-06-03 15:00:00', '#1cc88a', 'completed'),
(1, 'Code Review', 'Review pull requests', '2026-06-05 11:00:00', '2026-06-05 12:00:00', '#f6c23e', 'pending'),
(2, 'Workshop', 'Tech workshop on Laravel', '2026-06-10 13:00:00', '2026-06-10 16:00:00', '#36b9cc', 'pending');