-- Pilih database RIETS lama di phpMyAdmin sebelum menjalankan migrasi ini.

-- Jalankan hanya untuk database RIETS lama keluaran 29 Juli 2026.
ALTER TABLE employees ADD COLUMN tracking_pin_hash VARCHAR(255) NULL AFTER device_imei;
ALTER TABLE buildings ADD COLUMN land_area_m2 DECIMAL(12,2) NULL AFTER area_m2,
 ADD COLUMN building_area_m2 DECIMAL(12,2) NULL AFTER land_area_m2,
 ADD COLUMN total_floors INT UNSIGNED DEFAULT 1 AFTER building_area_m2,
 ADD COLUMN status ENUM('active','inactive') DEFAULT 'active' AFTER gis_file;
ALTER TABLE floors ADD COLUMN length_m DECIMAL(10,2) NULL AFTER height_px,
 ADD COLUMN width_m DECIMAL(10,2) NULL AFTER length_m,
 ADD COLUMN status ENUM('active','inactive') DEFAULT 'active' AFTER origin_lng;
ALTER TABLE rooms ADD COLUMN color CHAR(7) DEFAULT '#B9D8F2' AFTER polygon,
 ADD COLUMN status ENUM('active','inactive') DEFAULT 'active' AFTER is_restricted;
CREATE TABLE IF NOT EXISTS qr_areas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_id BIGINT UNSIGNED NULL, building_id BIGINT UNSIGNED NULL,
 code VARCHAR(80) UNIQUE NOT NULL, name VARCHAR(120) NOT NULL, latitude DECIMAL(10,7) NOT NULL,
 longitude DECIMAL(10,7) NOT NULL, radius_m DECIMAL(8,2) DEFAULT 100, status ENUM('active','inactive') DEFAULT 'active',
 scan_count BIGINT UNSIGNED DEFAULT 0, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE SET NULL, FOREIGN KEY(building_id) REFERENCES buildings(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(status), INDEX(latitude,longitude)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tracking_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, qr_area_id BIGINT UNSIGNED NULL,
 device_id BIGINT UNSIGNED NULL, public_id CHAR(36) UNIQUE NOT NULL, token_hash CHAR(64) UNIQUE NOT NULL,
 source VARCHAR(30) DEFAULT 'gps', status ENUM('active','stopped','expired') DEFAULT 'active', consent_at DATETIME NULL,
 ip_address VARCHAR(45), user_agent VARCHAR(500), started_at DATETIME NOT NULL, last_ping_at DATETIME NULL,
 ended_at DATETIME NULL, stop_reason VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id), FOREIGN KEY(qr_area_id) REFERENCES qr_areas(id) ON DELETE SET NULL,
 FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL, INDEX(employee_id,status), INDEX(last_ping_at)
) ENGINE=InnoDB;

ALTER TABLE current_locations
 MODIFY floor_id BIGINT UNSIGNED NULL, MODIFY x DECIMAL(10,2) NULL, MODIFY y DECIMAL(10,2) NULL,
 ADD COLUMN tracking_session_id BIGINT UNSIGNED NULL AFTER employee_id,
 ADD COLUMN source VARCHAR(30) NOT NULL DEFAULT 'gps' AFTER tracking_session_id,
 ADD COLUMN latitude DECIMAL(10,7) NULL AFTER room_id, ADD COLUMN longitude DECIMAL(10,7) NULL AFTER latitude,
 ADD COLUMN altitude_m DECIMAL(9,2) NULL AFTER longitude, ADD COLUMN heading_deg DECIMAL(6,2) NULL AFTER accuracy_m,
 ADD CONSTRAINT current_locations_session_fk FOREIGN KEY(tracking_session_id) REFERENCES tracking_sessions(id) ON DELETE SET NULL;

ALTER TABLE location_history
 MODIFY floor_id BIGINT UNSIGNED NULL, MODIFY x DECIMAL(10,2) NULL, MODIFY y DECIMAL(10,2) NULL,
 ADD COLUMN tracking_session_id BIGINT UNSIGNED NULL AFTER employee_id,
 ADD COLUMN source VARCHAR(30) NOT NULL DEFAULT 'gps' AFTER tracking_session_id,
 ADD COLUMN latitude DECIMAL(10,7) NULL AFTER room_id, ADD COLUMN longitude DECIMAL(10,7) NULL AFTER latitude,
 ADD COLUMN altitude_m DECIMAL(9,2) NULL AFTER longitude, ADD COLUMN heading_deg DECIMAL(6,2) NULL AFTER accuracy_m,
 ADD COLUMN client_event_id VARCHAR(80) NULL AFTER speed_mps, ADD COLUMN metadata JSON NULL AFTER received_at,
 ADD UNIQUE KEY location_event_unique(employee_id,client_event_id),
 ADD CONSTRAINT location_history_session_fk FOREIGN KEY(tracking_session_id) REFERENCES tracking_sessions(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS gps_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, location_history_id BIGINT UNSIGNED NOT NULL,
 latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, altitude_m DECIMAL(9,2), accuracy_m DECIMAL(8,2),
 heading_deg DECIMAL(6,2), speed_mps DECIMAL(8,2), provider VARCHAR(30) DEFAULT 'browser',
 FOREIGN KEY(location_history_id) REFERENCES location_history(id) ON DELETE CASCADE
) ENGINE=InnoDB;