CREATE TABLE IF NOT EXISTS `patrol_points` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `point_name` VARCHAR(255) NOT NULL,
    `qr_code` VARCHAR(500) UNIQUE NOT NULL,
    `latitude` DECIMAL(10, 8),
    `longitude` DECIMAL(11, 8),
    `location_description` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    INDEX `post_id` (`post_id`),
    INDEX `qr_code` (`qr_code`),
    INDEX `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `patrol_scans` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `patrol_point_id` INT NOT NULL,
    `guard_id` INT NOT NULL,
    `assignment_id` INT,
    `scan_timestamp` DATETIME NOT NULL,
    `scan_hour` VARCHAR(13),
    `latitude` DECIMAL(10, 8),
    `longitude` DECIMAL(11, 8),
    `device_info` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`patrol_point_id`) REFERENCES `patrol_points`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`guard_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assignment_id`) REFERENCES `guard_post_assignments`(`id`) ON DELETE SET NULL,
    INDEX `post_id` (`post_id`),
    INDEX `guard_id` (`guard_id`),
    INDEX `scan_hour` (`scan_hour`),
    INDEX `scan_timestamp` (`scan_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `patrols` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `patrol_hour` VARCHAR(13),
    `patrol_date` DATE NOT NULL,
    `patrol_start_time` DATETIME NOT NULL,
    `patrol_end_time` DATETIME NOT NULL,
    `status` ENUM('Missed', 'Incomplete', 'Completed') DEFAULT 'Missed',
    `total_points` INT DEFAULT 4,
    `total_scans` INT DEFAULT 0,
    `score` INT DEFAULT 0,
    `guards_assigned` INT DEFAULT 0,
    `notes` TEXT,
    `reminder_sent` BOOLEAN DEFAULT FALSE,
    `completion_percentage` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    INDEX `post_id` (`post_id`),
    INDEX `patrol_hour` (`patrol_hour`),
    INDEX `patrol_date` (`patrol_date`),
    INDEX `status` (`status`),
    UNIQUE KEY `unique_patrol` (`post_id`, `patrol_hour`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `patrol_point_status` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `patrol_id` INT NOT NULL,
    `patrol_point_id` INT NOT NULL,
    `scan_count` INT DEFAULT 0,
    `max_scans` INT DEFAULT 2,
    `score` INT DEFAULT 0,
    `status` ENUM('Missed', 'Incomplete', 'Completed') DEFAULT 'Missed',
    `first_scan_time` DATETIME,
    `last_scan_time` DATETIME,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`patrol_id`) REFERENCES `patrols`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`patrol_point_id`) REFERENCES `patrol_points`(`id`) ON DELETE CASCADE,
    INDEX `patrol_id` (`patrol_id`),
    INDEX `status` (`status`),
    UNIQUE KEY `unique_point_status` (`patrol_id`, `patrol_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
