CREATE DATABASE IF NOT EXISTS call_monitor CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE call_monitor;

CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    device_uuid VARCHAR(100),
    api_token VARCHAR(255),
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE consents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_uuid VARCHAR(100) NOT NULL,
    employee_name VARCHAR(150) NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    agreed_at TIMESTAMP NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE call_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_uuid VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    phone_number VARCHAR(30),
    call_type ENUM('INCOMING','OUTGOING','MISSED','UNKNOWN') DEFAULT 'UNKNOWN',
    start_timestamp BIGINT NOT NULL,
    duration_seconds INT DEFAULT 0,
    recording_file_name VARCHAR(255),
    recording_uploaded TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_employee (employee_id),
    INDEX idx_device (device_uuid)
);

CREATE TABLE recordings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_uuid VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    file_size_bytes BIGINT DEFAULT 0,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_on_device TINYINT(1) DEFAULT 0,
    deleted_detected_at TIMESTAMP NULL,
    INDEX idx_employee (employee_id)
);

CREATE TABLE deletion_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_uuid VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    recording_file_name VARCHAR(255) NOT NULL,
    detected_at TIMESTAMP NOT NULL,
    note TEXT,
    reviewed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE device_heartbeats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_uuid VARCHAR(100) NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    battery_level INT,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_device (device_uuid)
);

-- ملحوظة: لا يوجد أدمن افتراضي هنا عمدًا. شغّل ملف setup_admin.php مرة واحدة
-- بعد رفع المشروع لإنشاء أول حساب أدمن بباسورد حقيقي مشفّر (bcrypt) -
-- ده أأمن من وضع هاش جاهز في السكريبت.