-- Create database (run this first if database not created)
-- CREATE DATABASE patrol_db;
-- USE patrol_db;

-- Table 1: users
CREATE TABLE IF NOT EXISTS `users` (
    `user_id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `full_name` VARCHAR(100) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_admin` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 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 admin user (password = admin123)
-- Password hash is for "admin123" using password_hash()
INSERT INTO `users` (`username`, `full_name`, `password_hash`, `is_admin`) VALUES
('admin', 'Administrator', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- Insert 10 guards (password = guard123 for all)
INSERT INTO `users` (`username`, `full_name`, `password_hash`, `is_admin`) VALUES
('guard01', 'Ahmad Setiawan', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard02', 'Budi Santoso', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard03', 'Citra Dewi', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard04', 'Dedi Kurniawan', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard05', 'Eka Fitriani', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard06', 'Fajar Nugroho', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard07', 'Gina Lestari', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard08', 'Hendra Wijaya', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard09', 'Indah Permata', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0),
('guard10', 'Joko Susilo', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0);

-- 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);