-- Initial Schema for User Management

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,
  `profile_picture` VARCHAR(255) DEFAULT NULL,
  `is_suspended` BOOLEAN DEFAULT FALSE,
  `api_allowed` BOOLEAN DEFAULT TRUE,
  `last_login` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `groups` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user_groups` (
  `user_id` INT,
  `group_id` INT,
  PRIMARY KEY (`user_id`, `group_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `group_permissions` (
  `group_id` INT,
  `permission_id` INT,
  PRIMARY KEY (`group_id`, `permission_id`),
  FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `login_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT,
  `ip_address` VARCHAR(45),
  `login_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('success', 'failed'),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `active_sessions` (
  `session_id` VARCHAR(128) NOT NULL PRIMARY KEY,
  `user_id` INT NOT NULL UNIQUE,
  `login_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ip_address` VARCHAR(45),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Default Data
INSERT INTO `groups` (`id`, `name`, `description`) VALUES
(1, 'SuperAdmin', 'Full access to all system features.'),
(2, 'Admin', 'Administrative access to most features.'),
(3, 'User', 'Standard user access.');

-- Default SuperAdmin User (password: password123)
INSERT INTO `users` (`id`, `name`, `email`, `password`) VALUES
(1, 'Super Admin', 'admin@example.com', '$2y$10$fA.o.f8b.5L9zJ/Zc.915eB0GA9gISfCj2L5kI/A5d.4b5I3.f0Fm');

INSERT INTO `user_groups` (`user_id`, `group_id`) VALUES (1, 1);