-- =====================================================
-- MIGRATION SQL: ATTENDANCE FEATURES
-- Izin Telat, Izin Pulang Cepat, dan Koreksi Absen
-- =====================================================
-- 
-- Deskripsi:
-- File SQL ini menggabungkan semua migration untuk:
-- 1. Tabel attendance_corrections (koreksi absen)
-- 2. Tabel early_leave_requests (izin pulang cepat)
-- 3. Tabel late_arrival_requests (izin telat)
-- 4. Kolom quota settings di tabel companies
-- 
-- Cara penggunaan:
-- 1. Buka phpMyAdmin
-- 2. Pilih database attendance_nizhamia
-- 3. Buka tab SQL
-- 4. Copy-paste seluruh isi file ini
-- 5. Klik Go/Execute
-- 
-- Catatan:
-- - Menggunakan CREATE TABLE IF NOT EXISTS untuk keamanan
-- - Foreign keys akan memastikan integritas data
-- - Jika ada error karena tabel/kolom sudah ada, tidak akan merusak data
-- =====================================================

-- =====================================================
-- 1. CREATE attendance_corrections TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS `attendance_corrections` (
  `id` char(36) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `attendance_id` char(36) DEFAULT NULL,
  
  -- Target date for correction
  `target_date` date NOT NULL,
  
  -- Correction type: check_in, check_out, both, duration
  `correction_type` varchar(255) NOT NULL DEFAULT 'duration',
  
  -- Original values (stored for audit comparison)
  `original_check_in` time DEFAULT NULL,
  `original_check_out` time DEFAULT NULL,
  `original_duration` int(11) DEFAULT NULL COMMENT 'in minutes',
  
  -- Requested correction values
  `corrected_check_in` time DEFAULT NULL,
  `corrected_check_out` time DEFAULT NULL,
  `corrected_duration` int(11) DEFAULT NULL COMMENT 'in minutes',
  
  -- Reason for correction
  `reason` text DEFAULT NULL,
  
  -- Status: pending, approved, rejected, auto_approved
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  
  -- Approval info
  `approved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  
  -- For tracking "simulated" request date (when backdate mode is enabled)
  `simulated_request_at` timestamp NULL DEFAULT NULL,
  
  -- Flag to indicate if this was auto-approved (for backdate mode)
  `is_auto_approved` tinyint(1) NOT NULL DEFAULT 0,
  
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  
  -- Indexes
  KEY `attendance_corrections_user_id_index` (`user_id`),
  KEY `attendance_corrections_company_id_index` (`company_id`),
  KEY `attendance_corrections_target_date_index` (`target_date`),
  KEY `attendance_corrections_status_index` (`status`),
  KEY `attendance_corrections_created_at_index` (`created_at`),
  KEY `attendance_corrections_attendance_id_index` (`attendance_id`),
  KEY `attendance_corrections_approved_by_index` (`approved_by`),
  
  -- Foreign keys
  CONSTRAINT `attendance_corrections_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `attendance_corrections_company_id_foreign` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `attendance_corrections_attendance_id_foreign` 
    FOREIGN KEY (`attendance_id`) 
    REFERENCES `attendances` (`id`) 
    ON DELETE SET NULL,
    
  CONSTRAINT `attendance_corrections_approved_by_foreign` 
    FOREIGN KEY (`approved_by`) 
    REFERENCES `users` (`id`) 
    ON DELETE SET NULL
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- 2. CREATE early_leave_requests TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS `early_leave_requests` (
  `id` char(36) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `attendance_id` char(36) DEFAULT NULL,
  
  -- Request date (the day they want to leave early)
  `request_date` date NOT NULL,
  
  -- Planned leave time
  `planned_leave_time` time NOT NULL,
  
  -- Reason for early leave
  `reason_type` varchar(255) NOT NULL COMMENT 'sick, family, appointment, emergency, other',
  `reason_detail` text DEFAULT NULL,
  
  -- Status: pending, approved, rejected, used, expired
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  
  -- Approval info
  `approved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  
  -- Actual checkout info (filled when used)
  `actual_checkout_at` timestamp NULL DEFAULT NULL,
  
  -- For backdate mode - simulated timestamps
  `simulated_request_at` timestamp NULL DEFAULT NULL,
  `is_auto_approved` tinyint(1) NOT NULL DEFAULT 0,
  
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  
  -- Indexes
  KEY `early_leave_requests_user_id_index` (`user_id`),
  KEY `early_leave_requests_company_id_index` (`company_id`),
  KEY `early_leave_requests_request_date_index` (`request_date`),
  KEY `early_leave_requests_status_index` (`status`),
  KEY `early_leave_requests_attendance_id_index` (`attendance_id`),
  KEY `early_leave_requests_approved_by_index` (`approved_by`),
  
  -- Foreign keys
  CONSTRAINT `early_leave_requests_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `early_leave_requests_company_id_foreign` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `early_leave_requests_attendance_id_foreign` 
    FOREIGN KEY (`attendance_id`) 
    REFERENCES `attendances` (`id`) 
    ON DELETE SET NULL,
    
  CONSTRAINT `early_leave_requests_approved_by_foreign` 
    FOREIGN KEY (`approved_by`) 
    REFERENCES `users` (`id`) 
    ON DELETE SET NULL
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- 3. CREATE late_arrival_requests TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS `late_arrival_requests` (
  `id` char(36) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `attendance_id` char(36) DEFAULT NULL,
  
  -- Request details
  `request_date` date NOT NULL,
  `planned_arrival_time` time NOT NULL COMMENT 'Jam rencana datang',
  
  -- Reason
  `reason_type` varchar(255) NOT NULL COMMENT 'traffic, sick, family, appointment, emergency, official, other',
  `reason_detail` text DEFAULT NULL,
  
  -- Status & Approval
  `status` varchar(255) NOT NULL DEFAULT 'pending' COMMENT 'pending, approved, rejected, used, expired',
  `approved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  
  -- Actual check-in (filled when used)
  `actual_checkin_at` timestamp NULL DEFAULT NULL,
  
  -- For backdate mode
  `simulated_request_at` timestamp NULL DEFAULT NULL,
  `is_auto_approved` tinyint(1) NOT NULL DEFAULT 0,
  
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  
  -- Indexes
  KEY `late_arrival_requests_user_id_request_date_index` (`user_id`, `request_date`),
  KEY `late_arrival_requests_company_id_status_index` (`company_id`, `status`),
  KEY `late_arrival_requests_request_date_index` (`request_date`),
  KEY `late_arrival_requests_user_id_index` (`user_id`),
  KEY `late_arrival_requests_company_id_index` (`company_id`),
  KEY `late_arrival_requests_attendance_id_index` (`attendance_id`),
  KEY `late_arrival_requests_approved_by_index` (`approved_by`),
  
  -- Foreign keys
  CONSTRAINT `late_arrival_requests_user_id_foreign` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `late_arrival_requests_company_id_foreign` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE CASCADE,
    
  CONSTRAINT `late_arrival_requests_attendance_id_foreign` 
    FOREIGN KEY (`attendance_id`) 
    REFERENCES `attendances` (`id`) 
    ON DELETE SET NULL,
    
  CONSTRAINT `late_arrival_requests_approved_by_foreign` 
    FOREIGN KEY (`approved_by`) 
    REFERENCES `users` (`id`) 
    ON DELETE SET NULL
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- 4. ADD QUOTA SETTINGS TO companies TABLE
-- =====================================================
-- IMPORTANT: 
-- - Jika kolom sudah ada, query akan error tapi tidak akan merusak data
-- - Untuk MySQL/MariaDB lama yang tidak support IF NOT EXISTS, 
--   jalankan query satu per satu dan skip yang error
-- - Atau gunakan versi yang support IF NOT EXISTS (MySQL 8.0+, MariaDB 10.6+)

-- Correction quota settings
-- Skip jika kolom sudah ada (akan error tapi aman)
ALTER TABLE `companies`
ADD COLUMN `correction_quota_max` INT NULL 
  COMMENT 'Maximum correction requests per period. NULL = unlimited'
AFTER `backdate_enabled`;

ALTER TABLE `companies`
ADD COLUMN `correction_quota_period` VARCHAR(255) NOT NULL DEFAULT 'monthly'
  COMMENT 'Quota period: daily, weekly, monthly, yearly'
AFTER `correction_quota_max`;

-- Early leave quota settings
ALTER TABLE `companies`
ADD COLUMN `early_leave_quota_max` INT NULL
  COMMENT 'Maximum early leave requests per period. NULL = unlimited'
AFTER `correction_quota_period`;

ALTER TABLE `companies`
ADD COLUMN `early_leave_quota_period` VARCHAR(255) NOT NULL DEFAULT 'monthly'
  COMMENT 'Quota period: daily, weekly, monthly, yearly'
AFTER `early_leave_quota_max`;

-- Late arrival quota settings
ALTER TABLE `companies`
ADD COLUMN `late_arrival_quota_max` INT NULL
  COMMENT 'Maximum late arrival requests per period. NULL = unlimited'
AFTER `early_leave_quota_period`;

ALTER TABLE `companies`
ADD COLUMN `late_arrival_quota_period` VARCHAR(255) NOT NULL DEFAULT 'monthly'
  COMMENT 'Quota period: daily, weekly, monthly, yearly'
AFTER `late_arrival_quota_max`;


-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================
-- Uncomment baris di bawah untuk verifikasi setelah menjalankan semua query:

-- -- Check tables created
-- SELECT 
--   TABLE_NAME,
--   TABLE_ROWS,
--   CREATE_TIME
-- FROM information_schema.TABLES 
-- WHERE TABLE_SCHEMA = DATABASE() 
--   AND TABLE_NAME IN (
--     'attendance_corrections',
--     'early_leave_requests',
--     'late_arrival_requests'
--   )
-- ORDER BY TABLE_NAME;

-- -- Check columns in companies table
-- SELECT 
--   COLUMN_NAME,
--   DATA_TYPE,
--   IS_NULLABLE,
--   COLUMN_DEFAULT,
--   COLUMN_COMMENT
-- FROM information_schema.COLUMNS 
-- WHERE TABLE_SCHEMA = DATABASE() 
--   AND TABLE_NAME = 'companies'
--   AND COLUMN_NAME IN (
--     'correction_quota_max',
--     'correction_quota_period',
--     'early_leave_quota_max',
--     'early_leave_quota_period',
--     'late_arrival_quota_max',
--     'late_arrival_quota_period'
--   )
-- ORDER BY ORDINAL_POSITION;

-- -- Check foreign keys
-- SELECT 
--   TABLE_NAME,
--   CONSTRAINT_NAME,
--   REFERENCED_TABLE_NAME,
--   REFERENCED_COLUMN_NAME
-- FROM information_schema.KEY_COLUMN_USAGE
-- WHERE TABLE_SCHEMA = DATABASE()
--   AND TABLE_NAME IN (
--     'attendance_corrections',
--     'early_leave_requests',
--     'late_arrival_requests'
--   )
--   AND REFERENCED_TABLE_NAME IS NOT NULL
-- ORDER BY TABLE_NAME, CONSTRAINT_NAME;