-- =============================================
-- QR Code Attendance System - SQL Migration
-- =============================================
-- Run this SQL on your MySQL database
-- Make sure to run in order (1, 2, 3, 4)
-- =============================================

-- =============================================
-- 1. Create employee_cards table
-- =============================================
CREATE TABLE IF NOT EXISTS `employee_cards` (
    `id` CHAR(36) NOT NULL PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `card_number` VARCHAR(20) NOT NULL UNIQUE,
    `qr_payload` JSON NULL,
    `qr_signature` VARCHAR(64) NULL,
    `card_status` ENUM('active', 'revoked', 'expired') NOT NULL DEFAULT 'active',
    `issued_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `revoked_at` TIMESTAMP NULL,
    `revoked_reason` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    INDEX `employee_cards_user_id_index` (`user_id`),
    INDEX `employee_cards_company_id_index` (`company_id`),
    INDEX `employee_cards_card_status_index` (`card_status`),

    CONSTRAINT `employee_cards_user_id_foreign`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `employee_cards_company_id_foreign`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- 2. Create qr_attendance_logs table
-- =============================================
CREATE TABLE IF NOT EXISTS `qr_attendance_logs` (
    `id` CHAR(36) NOT NULL PRIMARY KEY,
    `scanned_user_id` BIGINT UNSIGNED NOT NULL,
    `scanner_user_id` BIGINT UNSIGNED NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `attendance_id` CHAR(36) NULL,
    `employee_card_id` CHAR(36) NULL,
    `scan_type` ENUM('check_in', 'check_out') NOT NULL,
    `latitude` DECIMAL(10, 8) NOT NULL,
    `longitude` DECIMAL(11, 8) NOT NULL,
    `scan_status` ENUM('success', 'failed') NOT NULL,
    `failure_reason` VARCHAR(255) NULL,
    `device_info` JSON NULL,
    `scanned_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    INDEX `qr_attendance_logs_scanned_user_id_index` (`scanned_user_id`),
    INDEX `qr_attendance_logs_scanner_user_id_index` (`scanner_user_id`),
    INDEX `qr_attendance_logs_company_id_index` (`company_id`),
    INDEX `qr_attendance_logs_scanned_at_index` (`scanned_at`),
    INDEX `qr_attendance_logs_scan_status_index` (`scan_status`),

    CONSTRAINT `qr_attendance_logs_scanned_user_id_foreign`
        FOREIGN KEY (`scanned_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `qr_attendance_logs_scanner_user_id_foreign`
        FOREIGN KEY (`scanner_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `qr_attendance_logs_company_id_foreign`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `qr_attendance_logs_employee_card_id_foreign`
        FOREIGN KEY (`employee_card_id`) REFERENCES `employee_cards` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- 3. Create activity_assignment_rules table
-- =============================================
CREATE TABLE IF NOT EXISTS `activity_assignment_rules` (
    `id` CHAR(36) NOT NULL PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `rule_type` ENUM('all_users', 'specific_users', 'departments', 'positions') NOT NULL,
    `target_values` JSON NULL,
    `frequency` ENUM('daily', 'weekly') NOT NULL DEFAULT 'daily',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,

    INDEX `activity_assignment_rules_company_id_index` (`company_id`),
    INDEX `activity_assignment_rules_is_active_index` (`is_active`),

    CONSTRAINT `activity_assignment_rules_company_id_foreign`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- 4. Add QR settings columns to companies table
-- =============================================
ALTER TABLE `companies`
    ADD COLUMN IF NOT EXISTS `qr_signing_secret` VARCHAR(64) NULL AFTER `updated_at`,
    ADD COLUMN IF NOT EXISTS `qr_card_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `qr_signing_secret`,
    ADD COLUMN IF NOT EXISTS `qr_scanner_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `qr_card_enabled`,
    ADD COLUMN IF NOT EXISTS `qr_scanner_radius` INT NOT NULL DEFAULT 100 AFTER `qr_scanner_enabled`,
    ADD COLUMN IF NOT EXISTS `activity_mandatory_enabled` TINYINT(1) NOT NULL DEFAULT 1 AFTER `qr_scanner_radius`,
    ADD COLUMN IF NOT EXISTS `activity_mandatory_scope` ENUM('all', 'rules') NOT NULL DEFAULT 'all' AFTER `activity_mandatory_enabled`;

-- =============================================
-- 5. Insert migration records (optional - for Laravel)
-- =============================================
-- Uncomment if you want to mark these as run in Laravel migrations table
/*
INSERT INTO `migrations` (`migration`, `batch`) VALUES
    ('2026_01_24_100000_create_employee_cards_table', (SELECT COALESCE(MAX(batch), 0) + 1 FROM (SELECT batch FROM migrations) AS m)),
    ('2026_01_24_100001_create_qr_attendance_logs_table', (SELECT COALESCE(MAX(batch), 0) + 1 FROM (SELECT batch FROM migrations) AS m)),
    ('2026_01_24_100002_create_activity_assignment_rules_table', (SELECT COALESCE(MAX(batch), 0) + 1 FROM (SELECT batch FROM migrations) AS m)),
    ('2026_01_24_100003_add_qr_settings_to_companies_table', (SELECT COALESCE(MAX(batch), 0) + 1 FROM (SELECT batch FROM migrations) AS m));
*/

-- =============================================
-- VERIFICATION QUERIES
-- =============================================
-- Run these to verify the tables were created:

-- SELECT * FROM information_schema.tables WHERE table_name IN ('employee_cards', 'qr_attendance_logs', 'activity_assignment_rules');
-- DESCRIBE employee_cards;
-- DESCRIBE qr_attendance_logs;
-- DESCRIBE activity_assignment_rules;
-- SHOW COLUMNS FROM companies LIKE 'qr_%';
-- SHOW COLUMNS FROM companies LIKE 'activity_mandatory%';