-- =====================================================
-- Royal Group HR Dashboard - Complete SQL Dump
-- Database: royal_group_hr
-- Laravel 10 + MySQL
-- =====================================================

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

-- Create Database
CREATE DATABASE IF NOT EXISTS `royal_group_hr`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `royal_group_hr`;

-- =====================================================
-- Table: legal_entities
-- =====================================================
CREATE TABLE `legal_entities` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `legal_entities` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Royal Gold Pvt. Ltd.', NOW(), NOW());

-- =====================================================
-- Table: departments
-- =====================================================
CREATE TABLE `departments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `legal_entity_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `departments_legal_entity_id_foreign` (`legal_entity_id`),
  CONSTRAINT `departments_legal_entity_id_foreign`
    FOREIGN KEY (`legal_entity_id`) REFERENCES `legal_entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `departments` (`id`, `name`, `legal_entity_id`, `created_at`, `updated_at`) VALUES
(1, 'Accounts', 1, NOW(), NOW()),
(2, 'HR & Admin', 1, NOW(), NOW()),
(3, 'Sales', 1, NOW(), NOW()),
(4, 'IT', 1, NOW(), NOW());

-- =====================================================
-- Table: sections
-- =====================================================
CREATE TABLE `sections` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `department_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sections_department_id_foreign` (`department_id`),
  CONSTRAINT `sections_department_id_foreign`
    FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sections` (`id`, `name`, `department_id`, `created_at`, `updated_at`) VALUES
(1, 'Vat & Tax', 1, NOW(), NOW()),
(2, 'Payroll', 1, NOW(), NOW()),
(3, 'Recruitment', 2, NOW(), NOW()),
(4, 'Training', 2, NOW(), NOW()),
(5, 'Software', 4, NOW(), NOW());

-- =====================================================
-- Table: locations
-- =====================================================
CREATE TABLE `locations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `locations` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Showroom 98, CAM', NOW(), NOW()),
(2, 'Head Office, Dhaka', NOW(), NOW()),
(3, 'Branch Office, Chittagong', NOW(), NOW());

-- =====================================================
-- Table: employees
-- =====================================================
CREATE TABLE `employees` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rg_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `dob` date NOT NULL,
  `joining_date` date NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `blood_group` varchar(5) DEFAULT NULL,
  `contact_no` varchar(20) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `department_id` bigint(20) UNSIGNED DEFAULT NULL,
  `section_id` bigint(20) UNSIGNED DEFAULT NULL,
  `location_id` bigint(20) UNSIGNED DEFAULT NULL,
  `legal_entity_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employees_rg_id_unique` (`rg_id`),
  KEY `employees_department_id_foreign` (`department_id`),
  KEY `employees_section_id_foreign` (`section_id`),
  KEY `employees_location_id_foreign` (`location_id`),
  KEY `employees_legal_entity_id_foreign` (`legal_entity_id`),
  CONSTRAINT `employees_department_id_foreign`
    FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `employees_section_id_foreign`
    FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`) ON DELETE SET NULL,
  CONSTRAINT `employees_location_id_foreign`
    FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `employees_legal_entity_id_foreign`
    FOREIGN KEY (`legal_entity_id`) REFERENCES `legal_entities` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employees` (`id`, `rg_id`, `name`, `dob`, `joining_date`, `status`, `blood_group`, `contact_no`, `designation`, `photo`, `department_id`, `section_id`, `location_id`, `legal_entity_id`, `created_at`, `updated_at`) VALUES
(1, '308', 'Mohammad Mojahidul Islam', '1976-10-15', '2021-12-12', 'Active', 'A+', '0173-0013038', 'Assistant Manager', NULL, 1, 1, 1, 1, NOW(), NOW()),
(2, '309', 'Farhan Ahmed', '1985-03-22', '2020-06-01', 'Active', 'B+', '01711-223344', 'Senior Accountant', NULL, 1, 2, 1, 1, NOW(), NOW()),
(3, '310', 'Nusrat Jahan', '1990-07-10', '2019-01-15', 'Active', 'O+', '01812-334455', 'HR Manager', NULL, 2, 3, 2, 1, NOW(), NOW()),
(4, '311', 'Karim Uddin', '1988-11-05', '2022-03-10', 'Active', 'AB+', '01911-445566', 'Software Engineer', NULL, 4, 5, 2, 1, NOW(), NOW()),
(5, '312', 'Tasnim Akter', '1993-04-18', '2023-07-01', 'Active', 'A-', '01612-556677', 'Sales Executive', NULL, 3, NULL, 3, 1, NOW(), NOW());

-- =====================================================
-- Table: users
-- =====================================================
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','hr','employee') NOT NULL DEFAULT 'employee',
  `employee_id` bigint(20) UNSIGNED DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_employee_id_foreign` (`employee_id`),
  CONSTRAINT `users_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Passwords are all: "password" (bcrypt hashed)
INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `employee_id`, `created_at`, `updated_at`) VALUES
(1, 'Admin', 'admin@royalgroup.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', NULL, NOW(), NOW()),
(2, 'Mohammad Mojahidul Islam', 'mojahidul@royalgroup.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'hr', 1, NOW(), NOW()),
(3, 'Nusrat Jahan', 'nusrat@royalgroup.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'hr', 3, NOW(), NOW()),
(4, 'Farhan Ahmed', 'farhan@royalgroup.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'employee', 2, NOW(), NOW());

-- =====================================================
-- Table: leave_types
-- =====================================================
CREATE TABLE `leave_types` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `leave_types` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Casual Leave',    NOW(), NOW()),
(2, 'Sick Leave',      NOW(), NOW()),
(3, 'Maternity Leave', NOW(), NOW()),
(4, 'Paternity Leave', NOW(), NOW()),
(5, 'SPWP',            NOW(), NOW()),
(6, 'SPWOP',           NOW(), NOW());

-- =====================================================
-- Table: leaves
-- =====================================================
CREATE TABLE `leaves` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) UNSIGNED NOT NULL,
  `leave_type_id` bigint(20) UNSIGNED NOT NULL,
  `taken` int(11) NOT NULL DEFAULT 0,
  `total_allowed` int(11) NOT NULL DEFAULT 0,
  `year` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `leaves_employee_id_foreign` (`employee_id`),
  KEY `leaves_leave_type_id_foreign` (`leave_type_id`),
  CONSTRAINT `leaves_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leaves_leave_type_id_foreign`
    FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `leaves` (`employee_id`, `leave_type_id`, `taken`, `total_allowed`, `year`, `created_at`, `updated_at`) VALUES
(1, 1, 2, 10, 2025, NOW(), NOW()),
(1, 2, 1, 10, 2025, NOW(), NOW()),
(1, 3, 0, 10, 2025, NOW(), NOW()),
(1, 4, 0,  5, 2025, NOW(), NOW()),
(1, 5, 0,  5, 2025, NOW(), NOW()),
(1, 6, 0,  5, 2025, NOW(), NOW()),
(2, 1, 1, 10, 2025, NOW(), NOW()),
(2, 2, 0, 10, 2025, NOW(), NOW()),
(3, 1, 3, 10, 2025, NOW(), NOW()),
(3, 2, 2, 10, 2025, NOW(), NOW());

-- =====================================================
-- Table: attendances
-- =====================================================
CREATE TABLE `attendances` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `status` enum('P','A','Late','CL','ML','SPWP','SPWOP','Weekend','Holiday') NOT NULL DEFAULT 'P',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attendances_employee_id_foreign` (`employee_id`),
  CONSTRAINT `attendances_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `attendances` (`employee_id`, `date`, `status`, `created_at`, `updated_at`) VALUES
(1, DATE_SUB(CURDATE(), INTERVAL 0 DAY), 'P',       NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'A',       NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Late',    NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'CL',      NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'P',       NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'P',       NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 6 DAY), 'ML',      NOW(), NOW()),
(1, DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Weekend', NOW(), NOW()),
(2, DATE_SUB(CURDATE(), INTERVAL 0 DAY), 'P',       NOW(), NOW()),
(2, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'P',       NOW(), NOW()),
(2, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'P',       NOW(), NOW()),
(3, DATE_SUB(CURDATE(), INTERVAL 0 DAY), 'P',       NOW(), NOW()),
(3, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'Late',    NOW(), NOW()),
(4, DATE_SUB(CURDATE(), INTERVAL 0 DAY), 'A',       NOW(), NOW()),
(5, DATE_SUB(CURDATE(), INTERVAL 0 DAY), 'P',       NOW(), NOW());

-- =====================================================
-- Table: performance_scores
-- =====================================================
CREATE TABLE `performance_scores` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) UNSIGNED NOT NULL,
  `year` year(4) NOT NULL,
  `point` decimal(5,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `performance_scores_employee_id_foreign` (`employee_id`),
  CONSTRAINT `performance_scores_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `performance_scores` (`employee_id`, `year`, `point`, `created_at`, `updated_at`) VALUES
(1, 2024, 82.70, NOW(), NOW()),
(1, 2023, 80.00, NOW(), NOW()),
(1, 2022, 84.98, NOW(), NOW()),
(2, 2024, 78.50, NOW(), NOW()),
(2, 2023, 75.00, NOW(), NOW()),
(3, 2024, 91.00, NOW(), NOW()),
(3, 2023, 88.50, NOW(), NOW()),
(4, 2024, 70.25, NOW(), NOW());

-- =====================================================
-- Table: fixed_assets
-- =====================================================
CREATE TABLE `fixed_assets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `asset_code` varchar(100) DEFAULT NULL,
  `assigned_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fixed_assets_employee_id_foreign` (`employee_id`),
  CONSTRAINT `fixed_assets_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `fixed_assets` (`employee_id`, `name`, `asset_code`, `assigned_date`, `created_at`, `updated_at`) VALUES
(1, 'Laptop',       'LT-001', '2022-01-01', NOW(), NOW()),
(1, 'Mobile Phone', 'MP-001', '2022-01-01', NOW(), NOW()),
(2, 'Laptop',       'LT-002', '2020-06-15', NOW(), NOW()),
(3, 'Desktop PC',   'DC-001', '2019-02-01', NOW(), NOW()),
(4, 'Laptop',       'LT-003', '2022-03-15', NOW(), NOW()),
(4, 'Monitor',      'MN-001', '2022-03-15', NOW(), NOW());

-- =====================================================
-- Table: tasks
-- =====================================================
CREATE TABLE `tasks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_code` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `priority` enum('Low','Medium','High','Critical') NOT NULL DEFAULT 'Medium',
  `assigned_to` bigint(20) UNSIGNED DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('Pending','In Progress','Completed','Cancelled') NOT NULL DEFAULT 'Pending',
  `comments` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tasks_task_code_unique` (`task_code`),
  KEY `tasks_assigned_to_foreign` (`assigned_to`),
  CONSTRAINT `tasks_assigned_to_foreign`
    FOREIGN KEY (`assigned_to`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tasks` (`task_code`, `description`, `priority`, `assigned_to`, `start_date`, `due_date`, `status`, `comments`, `created_at`, `updated_at`) VALUES
('TASK-011', 'Prepare monthly VAT report',         'High',     1, DATE_SUB(CURDATE(),INTERVAL 5 DAY), DATE_ADD(CURDATE(),INTERVAL 2 DAY),  'In Progress', 'Need data from sales team', NOW(), NOW()),
('TASK-010', 'Payroll processing for February',    'Critical', 1, DATE_SUB(CURDATE(),INTERVAL 3 DAY), DATE_ADD(CURDATE(),INTERVAL 1 DAY),  'Pending',     'Awaiting final approval',   NOW(), NOW()),
('TASK-009', 'Employee onboarding documentation',  'Medium',   3, DATE_SUB(CURDATE(),INTERVAL 7 DAY), CURDATE(),                           'Completed',   'All docs collected',        NOW(), NOW()),
('TASK-008', 'Update HR policies',                 'Low',      3, DATE_SUB(CURDATE(),INTERVAL 10 DAY),DATE_ADD(CURDATE(),INTERVAL 5 DAY),  'In Progress', 'Draft ready for review',    NOW(), NOW()),
('TASK-007', 'Software module testing',            'High',     4, DATE_SUB(CURDATE(),INTERVAL 2 DAY), DATE_ADD(CURDATE(),INTERVAL 3 DAY),  'Pending',     'Unit tests pending',        NOW(), NOW()),
('TASK-006', 'Q4 sales target review',             'Medium',   5, DATE_SUB(CURDATE(),INTERVAL 4 DAY), DATE_ADD(CURDATE(),INTERVAL 4 DAY),  'In Progress', 'Collecting data',           NOW(), NOW()),
('TASK-005', 'Audit preparation',                  'Critical', 2, DATE_SUB(CURDATE(),INTERVAL 8 DAY), DATE_SUB(CURDATE(),INTERVAL 1 DAY),  'Completed',   'Audit done successfully',   NOW(), NOW());

-- =====================================================
-- Table: notices
-- =====================================================
CREATE TABLE `notices` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `published_date` date NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `notices` (`title`, `content`, `published_date`, `is_active`, `created_at`, `updated_at`) VALUES
('Annual Performance Review 2025', 'All employees are requested to submit their self-assessment forms by end of February 2025. Please coordinate with your respective department heads.', '2025-01-15', 1, NOW(), NOW()),
('Office Timing Update', 'New office hours will be 9:00 AM to 6:00 PM effective from February 1, 2025. Employees must ensure timely arrival. Late arrival will be marked accordingly.', '2025-01-20', 1, NOW(), NOW()),
('Eid Holiday Notice', 'The office will remain closed from March 28 to April 2, 2025 for Eid-ul-Fitr. Emergency contact numbers will be circulated separately.', '2025-03-01', 1, NOW(), NOW()),
('Health & Safety Training', 'Mandatory health and safety training has been scheduled for all staff on February 15, 2025 at 10:00 AM in the conference room.', '2025-02-01', 1, NOW(), NOW());

-- =====================================================
-- Table: awards
-- =====================================================
CREATE TABLE `awards` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `awarded_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `awards_employee_id_foreign` (`employee_id`),
  CONSTRAINT `awards_employee_id_foreign`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `awards` (`employee_id`, `title`, `description`, `awarded_date`, `created_at`, `updated_at`) VALUES
(1, 'Best Employee Q4 2024',    'Awarded for outstanding performance in VAT & tax compliance.',          '2024-12-31', NOW(), NOW()),
(3, 'Best HR Manager 2024',     'Recognized for exceptional recruitment and employee engagement.',        '2024-12-31', NOW(), NOW()),
(2, 'Most Accurate Accountant', 'Zero discrepancy in financial reports for the entire year 2024.',        '2024-12-15', NOW(), NOW()),
(4, 'Innovation Award 2024',    'For developing an automated reporting tool that saved 10 hours/week.',   '2024-11-30', NOW(), NOW());

-- =====================================================
-- Laravel migrations tracking table
-- =====================================================
CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2024_01_01_000000_create_users_table', 1),
('2024_01_01_000001_create_employees_table', 1);

-- =====================================================
-- Laravel sessions & cache tables (optional)
-- =====================================================
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;

-- =====================================================
-- ✅ Import Complete!
-- Login: admin@royalgroup.com / password
-- =====================================================