-- Expo WebView SaaS Database Schema

-- Kullanıcılar
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `role` ENUM('admin', 'user') DEFAULT 'user',
  `email_verified` TINYINT(1) DEFAULT 0,
  `email_verification_token` VARCHAR(255) DEFAULT NULL,
  `password_reset_token` VARCHAR(255) DEFAULT NULL,
  `password_reset_expires` DATETIME DEFAULT NULL,
  `credits` INT(11) DEFAULT 0,
  `total_credits_used` INT(11) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Uygulamalar
CREATE TABLE IF NOT EXISTS `apps` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `package_name` VARCHAR(255) NOT NULL,
  `webview_url` TEXT NOT NULL,
  `platform` ENUM('android', 'ios', 'both') DEFAULT 'both',
  `orientation` ENUM('portrait', 'landscape', 'both') DEFAULT 'portrait',
  `status_bar_color` VARCHAR(7) DEFAULT '#000000',
  `status` ENUM('draft', 'ready', 'built') DEFAULT 'draft',
  `expo_sdk_version` VARCHAR(20) DEFAULT '50.0.0',
  `app_json` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Splash Screen Ayarları
CREATE TABLE IF NOT EXISTS `app_splash` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `background_color` VARCHAR(7) DEFAULT '#FFFFFF',
  `enabled` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- App Icon
CREATE TABLE IF NOT EXISTS `app_icons` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `icon_url` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logo (Menü/Header için)
CREATE TABLE IF NOT EXISTS `app_logos` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `logo_url` VARCHAR(500) DEFAULT NULL,
  `enabled` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Menü Ayarları
CREATE TABLE IF NOT EXISTS `app_menus` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `menu_type` ENUM('drawer', 'bottom_tab', 'none') DEFAULT 'drawer',
  `menu_color` VARCHAR(7) DEFAULT '#FFFFFF',
  `icon_set` VARCHAR(50) DEFAULT 'material',
  `open_on_start` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Menü Elemanları
CREATE TABLE IF NOT EXISTS `app_menu_items` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `url` TEXT NOT NULL,
  `icon` VARCHAR(100) DEFAULT NULL,
  `order` INT(11) DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_app_id` (`app_id`),
  INDEX `idx_order` (`order`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- OneSignal Entegrasyonu
CREATE TABLE IF NOT EXISTS `app_onesignal` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `app_id_onesignal` VARCHAR(255) DEFAULT NULL,
  `rest_api_key` VARCHAR(255) DEFAULT NULL,
  `enabled` TINYINT(1) DEFAULT 0,
  `credit_used` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AdMob Entegrasyonu
CREATE TABLE IF NOT EXISTS `app_admob` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `android_banner_id` VARCHAR(255) DEFAULT NULL,
  `android_interstitial_id` VARCHAR(255) DEFAULT NULL,
  `ios_banner_id` VARCHAR(255) DEFAULT NULL,
  `ios_interstitial_id` VARCHAR(255) DEFAULT NULL,
  `enabled` TINYINT(1) DEFAULT 0,
  `display_frequency` INT(11) DEFAULT 30,
  `credit_used` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_app_id` (`app_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Build Geçmişi
CREATE TABLE IF NOT EXISTS `app_builds` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) UNSIGNED NOT NULL,
  `build_type` ENUM('apk', 'aab', 'ipa') NOT NULL,
  `build_status` ENUM('pending', 'building', 'completed', 'failed') DEFAULT 'pending',
  `expo_build_id` VARCHAR(255) DEFAULT NULL,
  `download_url` TEXT DEFAULT NULL,
  `error_message` TEXT DEFAULT NULL,
  `credit_used` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_app_id` (`app_id`),
  INDEX `idx_status` (`build_status`),
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kredi İşlem Geçmişi
CREATE TABLE IF NOT EXISTS `credit_transactions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `app_id` INT(11) UNSIGNED DEFAULT NULL,
  `transaction_type` ENUM('app_create', 'build', 'onesignal', 'admob', 'admin_add', 'admin_subtract') NOT NULL,
  `amount` INT(11) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_app_id` (`app_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`app_id`) REFERENCES `apps`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kredi Ayarları (Admin tarafından yönetilir)
CREATE TABLE IF NOT EXISTS `credit_settings` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `action_type` VARCHAR(50) NOT NULL UNIQUE,
  `credit_amount` INT(11) NOT NULL DEFAULT 1,
  `description` VARCHAR(255) DEFAULT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kredi ayarları için varsayılan değerler
INSERT INTO `credit_settings` (`action_type`, `credit_amount`, `description`) VALUES
('app_create', 1, 'Yeni uygulama oluşturma'),
('build', 1, 'Build alma (APK/AAB/IPA)'),
('onesignal', 1, 'OneSignal ilk entegrasyon'),
('admob', 1, 'AdMob ilk entegrasyon')
ON DUPLICATE KEY UPDATE `credit_amount` = VALUES(`credit_amount`);

-- Oturumlar (Session tabanlı auth için)
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_last_activity` (`last_activity`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kullanıcı Medya Kütüphanesi
CREATE TABLE IF NOT EXISTS `user_media` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_url` VARCHAR(500) NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_size` INT(11) UNSIGNED NOT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `width` INT(11) UNSIGNED DEFAULT NULL,
  `height` INT(11) UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_file_type` (`file_type`),
  INDEX `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;