-- Enterprise HRM ERP System - Database Schema
-- Simplified structure with roles, users, and user_sites

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- ============================================
-- ROLES TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_code` varchar(50) NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `role_description` text,
  `is_system_role` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USERS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `phone` varchar(50),
  `role_id` int(11) NOT NULL,
  `user_status` enum('active','inactive','locked') DEFAULT 'active',
  `last_login` datetime,
  `failed_login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_user_role` (`role_id`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SITES TABLE (for multi-site support)
-- ============================================

CREATE TABLE IF NOT EXISTS `sites` (
  `site_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_code` varchar(50) NOT NULL,
  `site_name` varchar(255) NOT NULL,
  `site_address` text,
  `site_phone` varchar(50),
  `site_email` varchar(255),
  `site_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`site_id`),
  UNIQUE KEY `site_code` (`site_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER_SITES TABLE (Many-to-Many relationship)
-- ============================================

CREATE TABLE IF NOT EXISTS `user_sites` (
  `user_site_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `is_primary` tinyint(1) DEFAULT 0,
  `assigned_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `assigned_by` int(11),
  PRIMARY KEY (`user_site_id`),
  UNIQUE KEY `unique_user_site` (`user_id`,`site_id`),
  KEY `fk_user_site_user` (`user_id`),
  KEY `fk_user_site_site` (`site_id`),
  KEY `fk_user_site_assigned_by` (`assigned_by`),
  CONSTRAINT `fk_user_site_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_site_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_site_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INITIAL DATA - ROLES
-- ============================================

-- Insert default roles
INSERT INTO `roles` (`role_id`, `role_code`, `role_name`, `role_description`, `is_system_role`) VALUES
(1, 'super_admin', 'Super Administrator', 'Full system access with all permissions', 1),
(2, 'hr_admin', 'HR Administrator', 'HR management and administration access', 1),
(3, 'manager', 'Manager', 'Department manager with limited access', 1),
(4, 'employee', 'Employee', 'Regular employee with self-service access', 1)
ON DUPLICATE KEY UPDATE 
  role_name = VALUES(role_name),
  role_description = VALUES(role_description);

-- ============================================
-- INITIAL DATA - DEFAULT SITE
-- ============================================

-- Insert default site
INSERT INTO `sites` (`site_id`, `site_code`, `site_name`, `site_status`) VALUES
(1, 'MAIN', 'Main Office', 'active')
ON DUPLICATE KEY UPDATE 
  site_name = VALUES(site_name);

COMMIT;

