-- FinalTech HRMS Sample Data
-- This file contains sample data for testing and demonstration

-- Insert additional branches
INSERT INTO `branches` (`name`, `address`, `phone`, `email`, `manager`) VALUES
('Branch Office North', '456 North Avenue, City, State 12346', '+1-555-0124', 'north@company.com', 'North Manager'),
('Branch Office South', '789 South Street, City, State 12347', '+1-555-0125', 'south@company.com', 'South Manager');

-- Insert additional departments
INSERT INTO `departments` (`name`, `description`, `branchId`, `headOfDepartment`) VALUES
('Human Resources', 'Employee management and development', 1, 'HR Manager'),
('Information Technology', 'IT support and development', 1, 'IT Manager'),
('Finance', 'Financial management and accounting', 1, 'Finance Manager'),
('Operations', 'Daily operations management', 2, 'Operations Manager'),
('Sales', 'Sales and customer relations', 3, 'Sales Manager');

-- Insert additional occupations
INSERT INTO `occupations` (`title`, `description`, `baseSalary`) VALUES
('HR Manager', 'Human resources management', 4500.00),
('IT Specialist', 'Information technology support', 4000.00),
('Accountant', 'Financial record keeping', 3500.00),
('Sales Representative', 'Customer sales and support', 3000.00),
('Operations Supervisor', 'Operations oversight', 3800.00),
('Administrative Assistant', 'Administrative support', 2500.00),
('Security Guard', 'Facility security', 2000.00),
('Maintenance Technician', 'Equipment maintenance', 2800.00);

-- Insert sample employees
INSERT INTO `employees` (`employeeNumber`, `firstName`, `lastName`, `email`, `phone`, `hireDate`, `branchId`, `departmentId`, `occupationId`, `employeeType`, `baseSalary`, `dailyRate`, `allowances`) VALUES
('EMP001', 'John', 'Smith', 'john.smith@company.com', '+1-555-1001', '2024-01-15', 1, 2, 2, 'permanent', 4500.00, NULL, 200.00),
('EMP002', 'Sarah', 'Johnson', 'sarah.johnson@company.com', '+1-555-1002', '2024-02-01', 1, 3, 3, 'permanent', 4000.00, NULL, 150.00),
('EMP003', 'Michael', 'Brown', 'michael.brown@company.com', '+1-555-1003', '2024-02-15', 1, 4, 4, 'permanent', 3500.00, NULL, 100.00),
('EMP004', 'Lisa', 'Davis', 'lisa.davis@company.com', '+1-555-1004', '2024-03-01', 2, 5, 5, 'permanent', 3800.00, NULL, 120.00),
('EMP005', 'Robert', 'Wilson', 'robert.wilson@company.com', '+1-555-1005', '2024-03-15', 3, 6, 6, 'permanent', 3000.00, NULL, 80.00),
('EMP006', 'Jennifer', 'Garcia', 'jennifer.garcia@company.com', '+1-555-1006', '2024-04-01', 1, 1, 7, 'permanent', 2500.00, NULL, 50.00),
('EMP007', 'David', 'Martinez', 'david.martinez@company.com', '+1-555-1007', '2024-04-15', 1, 1, 8, 'casual', NULL, 100.00, 0.00),
('EMP008', 'Emily', 'Anderson', 'emily.anderson@company.com', '+1-555-1008', '2024-05-01', 2, 5, 9, 'casual', NULL, 120.00, 0.00);

-- Insert sample attendance records (last 30 days)
INSERT INTO `attendance` (`employeeId`, `date`, `timeIn`, `timeOut`, `hoursWorked`, `status`) VALUES
-- Employee 1 (John Smith)
(1, '2024-12-01', '08:00:00', '17:00:00', 8.00, 'present'),
(1, '2024-12-02', '08:15:00', '17:00:00', 7.75, 'late'),
(1, '2024-12-03', '08:00:00', '17:00:00', 8.00, 'present'),
(1, '2024-12-04', '08:00:00', '17:30:00', 8.50, 'overtime'),
(1, '2024-12-05', '08:00:00', '17:00:00', 8.00, 'present'),

-- Employee 2 (Sarah Johnson)
(2, '2024-12-01', '08:30:00', '17:30:00', 8.00, 'present'),
(2, '2024-12-02', '08:00:00', '17:00:00', 8.00, 'present'),
(2, '2024-12-03', NULL, NULL, 0.00, 'absent'),
(2, '2024-12-04', '08:00:00', '17:00:00', 8.00, 'present'),
(2, '2024-12-05', '08:00:00', '13:00:00', 4.00, 'half_day'),

-- Employee 3 (Michael Brown)
(3, '2024-12-01', '08:00:00', '17:00:00', 8.00, 'present'),
(3, '2024-12-02', '08:00:00', '17:00:00', 8.00, 'present'),
(3, '2024-12-03', '08:00:00', '17:00:00', 8.00, 'present'),
(3, '2024-12-04', '08:00:00', '17:00:00', 8.00, 'present'),
(3, '2024-12-05', '08:00:00', '17:00:00', 8.00, 'present');

-- Insert sample payroll records
INSERT INTO `payroll` (`employeeId`, `payPeriodStart`, `payPeriodEnd`, `baseSalary`, `allowances`, `overtime`, `grossPay`, `taxDeduction`, `socialSecurityDeduction`, `totalDeductions`, `netPay`, `paymentStatus`) VALUES
(1, '2024-11-01', '2024-11-30', 4500.00, 200.00, 150.00, 4850.00, 485.00, 242.50, 727.50, 4122.50, 'paid'),
(2, '2024-11-01', '2024-11-30', 4000.00, 150.00, 0.00, 4150.00, 415.00, 207.50, 622.50, 3527.50, 'paid'),
(3, '2024-11-01', '2024-11-30', 3500.00, 100.00, 80.00, 3680.00, 368.00, 184.00, 552.00, 3128.00, 'paid');

-- Insert sample loan records
INSERT INTO `loans` (`employeeId`, `amount`, `interestRate`, `installmentAmount`, `remainingBalance`, `startDate`, `endDate`, `status`, `purpose`, `approvedBy`) VALUES
(1, 10000.00, 5.00, 500.00, 8500.00, '2024-10-01', '2026-09-30', 'active', 'Home renovation', 'admin'),
(3, 5000.00, 3.00, 250.00, 4250.00, '2024-11-01', '2026-10-31', 'active', 'Emergency medical expenses', 'admin');

-- Insert sample salary advance records
INSERT INTO `salary_advances` (`employeeId`, `amount`, `requestDate`, `approvalDate`, `status`, `reason`, `approvedBy`) VALUES
(2, 800.00, '2024-11-15', '2024-11-16', 'approved', 'Family emergency', 'admin'),
(4, 600.00, '2024-11-20', '2024-11-21', 'approved', 'Car repair', 'admin'),
(5, 1000.00, '2024-12-01', NULL, 'pending', 'Medical expenses', NULL);

-- Insert sample deduction records
INSERT INTO `deductions` (`employeeId`, `type`, `amount`, `description`, `date`, `status`, `approvedBy`) VALUES
(1, 'loan', 500.00, 'Monthly loan installment', '2024-12-01', 'approved', 'admin'),
(2, 'advance', 800.00, 'Salary advance repayment', '2024-12-01', 'approved', 'admin'),
(3, 'loan', 250.00, 'Monthly loan installment', '2024-12-01', 'approved', 'admin'),
(4, 'other', 50.00, 'Uniform replacement', '2024-11-25', 'approved', 'admin'),
(5, 'disciplinary', 100.00, 'Late arrival penalty', '2024-11-28', 'approved', 'admin');

-- Insert audit log samples
INSERT INTO `audit_logs` (`userId`, `action`, `tableName`, `recordId`, `newValues`, `ipAddress`) VALUES
('admin-001', 'CREATE', 'employees', '1', '{"firstName":"John","lastName":"Smith","employeeNumber":"EMP001"}', '192.168.1.100'),
('admin-001', 'UPDATE', 'employees', '1', '{"phone":"+1-555-1001"}', '192.168.1.100'),
('admin-001', 'CREATE', 'attendance', '1', '{"employeeId":1,"date":"2024-12-01","status":"present"}', '192.168.1.100');

-- Update company settings with more realistic data
UPDATE `company_settings` SET 
    `companyName` = 'FinalTech Solutions Ltd.',
    `address` = '456 Innovation Drive, Tech City, TC 12345',
    `phone` = '+1-555-TECH-HR',
    `email` = 'hr@finaltech.com',
    `website` = 'www.finaltech.com',
    `taxRate` = 10.00,
    `socialSecurityRate` = 5.00,
    `workingHours` = 8,
    `payrollFrequency` = 'monthly',
    `fiscalYearStart` = '2024-01-01',
    `timezone` = 'America/New_York',
    `currency` = 'USD',
    `dateFormat` = 'MM/DD/YYYY'
WHERE `id` = 1;