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

-- 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;

-- 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');
