-- ============================================================
-- MULTI-TENANT ATTENDANCE SYSTEM - DATABASE SCHEMA
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Companies (Tenants)
CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL UNIQUE COMMENT 'UUID',
  `name` VARCHAR(255) NOT NULL,
  `admin_username` VARCHAR(100) NOT NULL UNIQUE,
  `admin_password` VARCHAR(255) NOT NULL COMMENT 'bcrypt hash',
  `subscription_status` ENUM('active','suspended','trial') DEFAULT 'trial',
  `max_employees` INT DEFAULT 50,
  `language` VARCHAR(5) DEFAULT 'ar',
  `timezone` VARCHAR(50) DEFAULT 'Africa/Cairo',
  `company_logo` VARCHAR(500) DEFAULT NULL,
  `notification_email` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Super Admins
CREATE TABLE IF NOT EXISTS `super_admins` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL COMMENT 'bcrypt hash',
  `language` VARCHAR(5) DEFAULT 'ar',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Departments
CREATE TABLE IF NOT EXISTS `departments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `department_name` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Employees
CREATE TABLE IF NOT EXISTS `employees` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `department_id` INT UNSIGNED DEFAULT NULL,
  `employee_code` VARCHAR(20) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `national_id` VARCHAR(50),
  `phone` VARCHAR(20),
  `job_title` VARCHAR(100),
  `salary` DECIMAL(10,2) DEFAULT 0.00,
  `hire_date` DATE,
  `address` TEXT,
  `username` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL COMMENT 'bcrypt hash',
  `must_change_password` TINYINT(1) DEFAULT 0,
  `device_id` VARCHAR(255) DEFAULT NULL COMMENT 'bound device fingerprint',
  `device_bound_at` TIMESTAMP NULL,
  `branch_id` INT UNSIGNED DEFAULT NULL,
  `work_nature` ENUM('internal', 'external') DEFAULT 'internal',
  `is_active` TINYINT(1) DEFAULT 1,
  `language` VARCHAR(5) DEFAULT 'ar',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_company_username` (`company_id`, `username`),
  INDEX `idx_company_id` (`company_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE,
  FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Branches (Geofence locations per company)
CREATE TABLE IF NOT EXISTS `branches` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `latitude` DECIMAL(10, 8) NOT NULL,
  `longitude` DECIMAL(11, 8) NOT NULL,
  `radius` INT DEFAULT 100,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Legacy Location table (kept for backward compatibility or migration reference)
CREATE TABLE IF NOT EXISTS `clinic_locations` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL UNIQUE,
  `location_name` VARCHAR(255) DEFAULT 'Main Office',
  `latitude` DECIMAL(10, 8) NOT NULL,
  `longitude` DECIMAL(11, 8) NOT NULL,
  `allowed_radius_meters` INT DEFAULT 100,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Attendance Records
CREATE TABLE IF NOT EXISTS `attendance` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `employee_id` INT UNSIGNED NOT NULL,
  `type` ENUM('check_in','check_out') NOT NULL,
  `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `latitude` DECIMAL(10, 8),
  `longitude` DECIMAL(11, 8),
  `distance_meters` DECIMAL(8, 2) DEFAULT NULL COMMENT 'distance from office',
  `location_status` ENUM('ok','alert') DEFAULT 'ok',
  `selfie_path` VARCHAR(500) DEFAULT NULL,
  `device_id` VARCHAR(255),
  `qr_token` VARCHAR(512) COMMENT 'validated QR token used',
  `ip_address` VARCHAR(45),
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`),
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_timestamp` (`timestamp`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Leave Requests
CREATE TABLE IF NOT EXISTS `leave_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `employee_id` INT UNSIGNED NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `reason` TEXT,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `admin_note` TEXT,
  `reviewed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`),
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_status` (`status`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin Messages
CREATE TABLE IF NOT EXISTS `admin_messages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `employee_id` INT UNSIGNED NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`),
  INDEX `idx_employee_id` (`employee_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- QR Tokens (for dynamic, server-validated QR codes)
CREATE TABLE IF NOT EXISTS `qr_tokens` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36) NOT NULL,
  `token` VARCHAR(512) NOT NULL UNIQUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NOT NULL,
  `used` TINYINT(1) DEFAULT 0,
  INDEX `idx_token` (`token`),
  INDEX `idx_expires` (`expires_at`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit Log
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` VARCHAR(36),
  `actor_type` ENUM('super_admin','admin','employee') NOT NULL,
  `actor_id` VARCHAR(100),
  `action` VARCHAR(255) NOT NULL,
  `details` JSON,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- Seed super admin (password: SuperAdmin@2024)
INSERT INTO `super_admins` (`username`, `password`) VALUES
('superadmin', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
