-- =====================================================
-- GATECLOUD - Geçiş Kontrol Sistemi
-- Multi-Tenant SaaS Veritabanı Şeması
-- =====================================================


-- -----------------------------------------------------
-- Paket/Plan Tanımları
-- -----------------------------------------------------
CREATE TABLE `packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `code` varchar(20) NOT NULL,
  `max_devices` int(11) NOT NULL DEFAULT 1,
  `max_users` int(11) NOT NULL DEFAULT 50,
  `max_cards` int(11) NOT NULL DEFAULT 100,
  `max_admins` int(11) NOT NULL DEFAULT 2,
  `features` JSON,
  `monthly_price` decimal(10,2) NOT NULL DEFAULT 0,
  `yearly_price` decimal(10,2) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan paketler
INSERT INTO `packages` (`name`, `code`, `max_devices`, `max_users`, `max_cards`, `max_admins`, `features`, `monthly_price`, `yearly_price`) VALUES
('Başlangıç', 'starter', 1, 50, 100, 2, '{"realtime_logs": true, "basic_reports": true, "api_access": false, "multi_location": false}', 299.00, 2990.00),
('Pro', 'pro', 5, 200, 500, 5, '{"realtime_logs": true, "basic_reports": true, "advanced_reports": true, "api_access": true, "multi_location": true, "webhook": true}', 699.00, 6990.00),
('Kurumsal', 'enterprise', 999, 9999, 99999, 99, '{"realtime_logs": true, "basic_reports": true, "advanced_reports": true, "api_access": true, "multi_location": true, "webhook": true, "custom_integration": true, "priority_support": true}', 1499.00, 14990.00);

-- -----------------------------------------------------
-- Şirketler (Tenants)
-- -----------------------------------------------------
CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL COMMENT 'Giriş için şirket kodu',
  `tax_number` varchar(20) DEFAULT NULL,
  `tax_office` varchar(100) DEFAULT NULL,
  `address` text,
  `city` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `package_id` int(11) NOT NULL DEFAULT 1,
  `subscription_start` date DEFAULT NULL,
  `subscription_end` date DEFAULT NULL,
  `subscription_type` enum('monthly','yearly','trial') DEFAULT 'trial',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `settings` JSON,
  `pdks_api_key` varchar(64) DEFAULT NULL COMMENT 'PdksCloud entegrasyonu için',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `code` (`code`),
  KEY `package_id` (`package_id`),
  CONSTRAINT `fk_company_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Lokasyonlar/Şubeler
-- -----------------------------------------------------
CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` text,
  `city` varchar(100) DEFAULT NULL,
  `timezone` varchar(50) DEFAULT 'Europe/Istanbul',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_location_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Cihazlar (Hikvision Terminalleri)
-- -----------------------------------------------------
CREATE TABLE `devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `location_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT 'DS-K1T321MFWX',
  `ip_address` varchar(45) NOT NULL,
  `port` int(11) NOT NULL DEFAULT 80,
  `username` varchar(100) NOT NULL DEFAULT 'admin',
  `password` varchar(255) NOT NULL,
  `use_https` tinyint(1) NOT NULL DEFAULT 0,
  `direction` enum('in','out','both') DEFAULT 'both' COMMENT 'Giriş/Çıkış yönü',
  `last_sync` datetime DEFAULT NULL,
  `last_heartbeat` datetime DEFAULT NULL,
  `status` enum('online','offline','error') DEFAULT 'offline',
  `status_message` varchar(255) DEFAULT NULL,
  `firmware_version` varchar(50) DEFAULT NULL,
  `capabilities` JSON COMMENT 'Cihaz özellikleri (yüz, parmak izi, kart)',
  `settings` JSON,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `fk_device_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_device_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Sistem Yöneticileri (Platform Admin)
-- -----------------------------------------------------
CREATE TABLE `system_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `role` enum('superadmin','support') DEFAULT 'support',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Şirket Yöneticileri (Tenant Admin)
-- -----------------------------------------------------
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `role` enum('owner','admin','operator') DEFAULT 'operator',
  `permissions` JSON,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_username` (`company_id`, `username`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_admin_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Departmanlar
-- -----------------------------------------------------
CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `fk_department_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_department_parent` FOREIGN KEY (`parent_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Personel/Kullanıcılar (Geçiş Yapan Kişiler)
-- -----------------------------------------------------
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `employee_no` varchar(50) NOT NULL COMMENT 'Sicil No - Hikvision employeeNo',
  `card_no` varchar(50) DEFAULT NULL COMMENT 'Kart numarası',
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `tc_no` varchar(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `gender` enum('male','female') DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `job_title` varchar(255) DEFAULT NULL,
  `access_level` int(11) DEFAULT 1 COMMENT 'Erişim seviyesi',
  `valid_from` datetime DEFAULT NULL,
  `valid_until` datetime DEFAULT NULL,
  `has_face` tinyint(1) NOT NULL DEFAULT 0,
  `has_fingerprint` tinyint(1) NOT NULL DEFAULT 0,
  `has_card` tinyint(1) NOT NULL DEFAULT 0,
  `sync_status` enum('pending','synced','error') DEFAULT 'pending',
  `last_sync` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `notes` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_employee` (`company_id`, `employee_no`),
  KEY `company_id` (`company_id`),
  KEY `department_id` (`department_id`),
  KEY `card_no` (`card_no`),
  CONSTRAINT `fk_user_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Parmak İzi Verileri
-- -----------------------------------------------------
CREATE TABLE `fingerprints` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `finger_index` int(11) NOT NULL COMMENT '0-9 (0=sağ başparmak)',
  `template_data` text NOT NULL COMMENT 'Base64 encoded',
  `quality` int(11) DEFAULT NULL,
  `sync_status` enum('pending','synced','error') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_finger` (`user_id`, `finger_index`),
  CONSTRAINT `fk_fingerprint_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Yüz Verileri
-- -----------------------------------------------------
CREATE TABLE `faces` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `face_data` longtext COMMENT 'Base64 encoded image',
  `face_url` varchar(500) DEFAULT NULL,
  `quality` int(11) DEFAULT NULL,
  `sync_status` enum('pending','synced','error') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_face` (`user_id`),
  CONSTRAINT `fk_face_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Kartlar
-- -----------------------------------------------------
CREATE TABLE `cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) NOT NULL,
  `card_no` varchar(50) NOT NULL,
  `card_type` enum('normal','vip','visitor','temporary') DEFAULT 'normal',
  `valid_from` datetime DEFAULT NULL,
  `valid_until` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sync_status` enum('pending','synced','error') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_card` (`company_id`, `card_no`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_card_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_card_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Erişim Kuralları
-- -----------------------------------------------------
CREATE TABLE `access_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `time_start` time DEFAULT '00:00:00',
  `time_end` time DEFAULT '23:59:59',
  `days_of_week` varchar(20) DEFAULT '1,2,3,4,5,6,7' COMMENT 'Pazartesi=1',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_access_rule_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Kullanıcı-Cihaz Erişim İlişkisi
-- -----------------------------------------------------
CREATE TABLE `user_device_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `access_rule_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sync_status` enum('pending','synced','error') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_device` (`user_id`, `device_id`),
  KEY `device_id` (`device_id`),
  KEY `access_rule_id` (`access_rule_id`),
  CONSTRAINT `fk_uda_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uda_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uda_rule` FOREIGN KEY (`access_rule_id`) REFERENCES `access_rules` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Geçiş Logları (Ana Tablo)
-- -----------------------------------------------------
CREATE TABLE `access_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `employee_no` varchar(50) DEFAULT NULL,
  `card_no` varchar(50) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` enum('card','fingerprint','face','password','remote','button') DEFAULT 'card',
  `direction` enum('in','out','unknown') DEFAULT 'unknown',
  `verify_mode` varchar(50) DEFAULT NULL,
  `door_no` int(11) DEFAULT 1,
  `status` enum('success','denied','error') DEFAULT 'success',
  `error_code` int(11) DEFAULT NULL,
  `error_message` varchar(255) DEFAULT NULL,
  `temperature` decimal(4,1) DEFAULT NULL COMMENT 'Ateş ölçümü varsa',
  `mask_status` enum('unknown','wearing','not_wearing') DEFAULT 'unknown',
  `photo_url` varchar(500) DEFAULT NULL COMMENT 'Geçiş anı fotoğrafı',
  `raw_data` JSON COMMENT 'Cihazdan gelen ham veri',
  `synced_to_pdks` tinyint(1) NOT NULL DEFAULT 0,
  `pdks_sync_time` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `device_id` (`device_id`),
  KEY `user_id` (`user_id`),
  KEY `event_time` (`event_time`),
  KEY `idx_company_time` (`company_id`, `event_time`),
  KEY `idx_pdks_sync` (`company_id`, `synced_to_pdks`),
  CONSTRAINT `fk_log_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Partition by month için hazırlık (opsiyonel, büyük veri için)
-- ALTER TABLE access_logs PARTITION BY RANGE (TO_DAYS(event_time)) (...);

-- -----------------------------------------------------
-- Ziyaretçiler
-- -----------------------------------------------------
CREATE TABLE `visitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `tc_no` varchar(11) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `company_from` varchar(255) DEFAULT NULL COMMENT 'Geldiği firma',
  `visit_reason` varchar(255) DEFAULT NULL,
  `host_user_id` int(11) DEFAULT NULL COMMENT 'Ziyaret edilen kişi',
  `card_no` varchar(50) DEFAULT NULL COMMENT 'Geçici kart',
  `photo` varchar(255) DEFAULT NULL,
  `check_in` datetime DEFAULT NULL,
  `check_out` datetime DEFAULT NULL,
  `status` enum('expected','checked_in','checked_out','cancelled') DEFAULT 'expected',
  `notes` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `host_user_id` (`host_user_id`),
  CONSTRAINT `fk_visitor_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_visitor_host` FOREIGN KEY (`host_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Senkronizasyon Kuyruğu
-- -----------------------------------------------------
CREATE TABLE `sync_queue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `action` enum('add_user','update_user','delete_user','add_card','delete_card','add_face','delete_face','add_fingerprint','delete_fingerprint','door_open','door_close') NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` int(11) NOT NULL,
  `payload` JSON,
  `priority` int(11) DEFAULT 5,
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `attempts` int(11) DEFAULT 0,
  `max_attempts` int(11) DEFAULT 3,
  `error_message` text,
  `scheduled_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status_priority` (`status`, `priority`, `scheduled_at`),
  KEY `company_device` (`company_id`, `device_id`),
  CONSTRAINT `fk_sync_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sync_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Alarm/Olay Kayıtları
-- -----------------------------------------------------
CREATE TABLE `alarms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `device_id` int(11) DEFAULT NULL,
  `alarm_type` enum('door_forced','door_open_timeout','tamper','offline','auth_fail_multiple','fire','emergency') NOT NULL,
  `severity` enum('info','warning','critical') DEFAULT 'warning',
  `message` text,
  `event_time` datetime NOT NULL,
  `acknowledged` tinyint(1) NOT NULL DEFAULT 0,
  `acknowledged_by` int(11) DEFAULT NULL,
  `acknowledged_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `device_id` (`device_id`),
  KEY `event_time` (`event_time`),
  CONSTRAINT `fk_alarm_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_alarm_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Webhook Ayarları (Pro ve üstü paketler)
-- -----------------------------------------------------
CREATE TABLE `webhooks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `url` varchar(500) NOT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `events` JSON COMMENT '["access_log","alarm","user_sync"]',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_triggered` datetime DEFAULT NULL,
  `last_status` int(11) DEFAULT NULL,
  `fail_count` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_webhook_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- API Anahtarları
-- -----------------------------------------------------
CREATE TABLE `api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `api_key` varchar(64) NOT NULL,
  `api_secret` varchar(128) NOT NULL,
  `permissions` JSON,
  `rate_limit` int(11) DEFAULT 1000 COMMENT 'Saatlik limit',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_used` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_key` (`api_key`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_apikey_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Aktivite Logları (Audit Trail)
-- -----------------------------------------------------
CREATE TABLE `activity_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `system_admin_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `admin_id` (`admin_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Bildirimler
-- -----------------------------------------------------
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `admin_id` int(11) DEFAULT NULL COMMENT 'NULL = tüm adminler',
  `type` enum('info','warning','error','success') DEFAULT 'info',
  `title` varchar(255) NOT NULL,
  `message` text,
  `link` varchar(500) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_admin` (`company_id`, `admin_id`),
  KEY `is_read` (`is_read`),
  CONSTRAINT `fk_notification_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Oturum Yönetimi
-- -----------------------------------------------------
CREATE TABLE `sessions` (
  `id` varchar(128) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `system_admin_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `payload` text,
  `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `last_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- İlk sistem yöneticisi
-- -----------------------------------------------------
INSERT INTO `system_admins` (`username`, `email`, `password`, `name`, `role`) VALUES
('admin', 'admin@gatecloud.com.tr', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Sistem Yöneticisi', 'superadmin');
-- Varsayılan şifre: password

