

-- Table 2: locations (for geofencing)
CREATE TABLE IF NOT EXISTS `locations` (
    `location_id` VARCHAR(50) NOT NULL,
    `location_name` VARCHAR(100) NOT NULL,
    `expected_latitude` DECIMAL(10,8) NOT NULL,
    `expected_longitude` DECIMAL(11,8) NOT NULL,
    `radius_meters` INT DEFAULT 10,
    `is_active` TINYINT(1) DEFAULT 1,
    PRIMARY KEY (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table 3: patrol_logs
CREATE TABLE IF NOT EXISTS `patrol_logs` (
    `log_id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `location_id` VARCHAR(50) NOT NULL,
    `location_name` VARCHAR(100) NOT NULL,
    `latitude` DECIMAL(10,8) NOT NULL,
    `longitude` DECIMAL(11,8) NOT NULL,
    `scan_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `device_time` DATETIME NOT NULL,
    `sync_status` ENUM('synced','pending') DEFAULT 'synced',
    `geofence_ok` TINYINT(1) DEFAULT 0,
    `distance_meters` DECIMAL(10,2) DEFAULT NULL,
    PRIMARY KEY (`log_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
    FOREIGN KEY (`location_id`) REFERENCES `locations`(`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table 4: user_tokens
CREATE TABLE IF NOT EXISTS `user_tokens` (
    `token_id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `token` VARCHAR(255) NOT NULL UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `is_revoked` TINYINT(1) DEFAULT 0,
    PRIMARY KEY (`token_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert 10 sample locations (UPDATE THESE COORDINATES WITH YOUR REAL GPS!)
INSERT INTO `locations` (`location_id`, `location_name`, `expected_latitude`, `expected_longitude`, `radius_meters`) VALUES
('LOC_GATE_01', 'Main Gate', -6.20000000, 106.80000000, 10),
('LOC_GATE_02', 'East Gate', -6.20100000, 106.80500000, 10),
('LOC_GATE_03', 'West Gate', -6.19900000, 106.79500000, 10),
('LOC_BLDG_A', 'Building A Lobby', -6.20200000, 106.80200000, 10),
('LOC_BLDG_B', 'Building B Entrance', -6.20300000, 106.80300000, 10),
('LOC_PARKING', 'Parking Lot A', -6.19800000, 106.79900000, 15),
('LOC_WAREHOUSE', 'Warehouse Door', -6.20400000, 106.79800000, 10),
('LOC_SERVER', 'Server Room', -6.20250000, 106.80150000, 8),
('LOC_CAFETERIA', 'Cafeteria', -6.20150000, 106.80050000, 10),
('LOC_LOADING', 'Loading Dock', -6.20500000, 106.80400000, 10);