-- Create main database
CREATE DATABASE IF NOT EXISTS `market_manager` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user
CREATE USER IF NOT EXISTS 'market_user'@'%' IDENTIFIED BY 'changeme_strong_password';
GRANT ALL PRIVILEGES ON `market_manager`.* TO 'market_user'@'%';
GRANT ALL PRIVILEGES ON `tenant_%`.* TO 'market_user'@'%';
FLUSH PRIVILEGES;

-- Main database tables (landlord)
USE `market_manager`;

-- Tenants table (managed by stancl/tenancy)
CREATE TABLE IF NOT EXISTS `tenants` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `uuid` char(36) NOT NULL,
    `domain` varchar(255) DEFAULT NULL,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `status` enum('active','suspended','expired','trial') NOT NULL DEFAULT 'trial',
    `plan_id` bigint unsigned DEFAULT NULL,
    `subscription_ends_at` timestamp NULL DEFAULT NULL,
    `trial_ends_at` timestamp NULL DEFAULT NULL,
    `data` json DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `tenants_uuid_unique` (`uuid`),
    UNIQUE KEY `tenants_domain_unique` (`domain`),
    KEY `tenants_plan_id_foreign` (`plan_id`),
    KEY `tenants_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plans table
CREATE TABLE IF NOT EXISTS `plans` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `key` varchar(50) NOT NULL,
    `name` varchar(100) NOT NULL,
    `description` text DEFAULT NULL,
    `price_monthly_usdt` decimal(10,2) NOT NULL DEFAULT 0,
    `price_yearly_usdt` decimal(10,2) NOT NULL DEFAULT 0,
    `price_lifetime_usdt` decimal(10,2) NOT NULL DEFAULT 0,
    `features` json DEFAULT NULL,
    `limits` json DEFAULT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `sort_order` int NOT NULL DEFAULT 0,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `plans_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users (landlord - admin users)
CREATE TABLE IF NOT EXISTS `users` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `email_verified_at` timestamp NULL DEFAULT NULL,
    `password` varchar(255) NOT NULL,
    `role` enum('super_admin','admin','support','sales') NOT NULL DEFAULT 'admin',
    `two_factor_secret` text DEFAULT NULL,
    `two_factor_recovery_codes` text DEFAULT NULL,
    `remember_token` varchar(100) DEFAULT NULL,
    `current_team_id` bigint unsigned DEFAULT NULL,
    `profile_photo_path` varchar(2048) DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password reset tokens
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
    `email` varchar(255) NOT NULL,
    `token` varchar(255) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Personal access tokens (Sanctum)
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tokenable_type` varchar(255) NOT NULL,
    `tokenable_id` bigint unsigned NOT NULL,
    `name` varchar(255) NOT NULL,
    `token` varchar(64) NOT NULL,
    `abilities` text DEFAULT NULL,
    `last_used_at` timestamp NULL DEFAULT NULL,
    `expires_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
    KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
    KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Licenses (landlord)
CREATE TABLE IF NOT EXISTS `licenses` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned NOT NULL,
    `key` varchar(100) NOT NULL,
    `plan_key` varchar(50) NOT NULL,
    `status` enum('active','expired','revoked','pending') NOT NULL DEFAULT 'pending',
    `device_id` varchar(255) DEFAULT NULL,
    `device_name` varchar(255) DEFAULT NULL,
    `activated_at` timestamp NULL DEFAULT NULL,
    `expires_at` timestamp NULL DEFAULT NULL,
    `last_check_at` timestamp NULL DEFAULT NULL,
    `check_count` int unsigned NOT NULL DEFAULT 0,
    `metadata` json DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `licenses_key_unique` (`key`),
    KEY `licenses_tenant_id_foreign` (`tenant_id`),
    KEY `licenses_status_index` (`status`),
    KEY `licenses_device_id_index` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriptions (landlord)
CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned NOT NULL,
    `plan_id` bigint unsigned NOT NULL,
    `billing_cycle` enum('monthly','yearly','lifetime') NOT NULL,
    `status` enum('active','past_due','canceled','expired','trial') NOT NULL DEFAULT 'trial',
    `amount_usdt` decimal(10,2) NOT NULL,
    `currency` varchar(3) NOT NULL DEFAULT 'USDT',
    `network` enum('TRC20','ERC20') NOT NULL DEFAULT 'TRC20',
    `wallet_address` varchar(255) DEFAULT NULL,
    `starts_at` timestamp NOT NULL,
    `ends_at` timestamp NULL DEFAULT NULL,
    `trial_ends_at` timestamp NULL DEFAULT NULL,
    `canceled_at` timestamp NULL DEFAULT NULL,
    `auto_renew` tinyint(1) NOT NULL DEFAULT 1,
    `payment_gateway` varchar(50) DEFAULT NULL,
    `gateway_subscription_id` varchar(255) DEFAULT NULL,
    `metadata` json DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `subscriptions_tenant_id_foreign` (`tenant_id`),
    KEY `subscriptions_plan_id_foreign` (`plan_id`),
    KEY `subscriptions_status_index` (`status`),
    KEY `subscriptions_ends_at_index` (`ends_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments (landlord)
CREATE TABLE IF NOT EXISTS `payments` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned NOT NULL,
    `subscription_id` bigint unsigned DEFAULT NULL,
    `license_id` bigint unsigned DEFAULT NULL,
    `type` enum('subscription','license_renewal','license_new','manual') NOT NULL,
    `amount_usdt` decimal(10,2) NOT NULL,
    `currency` varchar(3) NOT NULL DEFAULT 'USDT',
    `network` enum('TRC20','ERC20') NOT NULL DEFAULT 'TRC20',
    `tx_hash` varchar(255) DEFAULT NULL,
    `from_address` varchar(255) DEFAULT NULL,
    `to_address` varchar(255) DEFAULT NULL,
    `confirmations` int unsigned NOT NULL DEFAULT 0,
    `required_confirmations` int unsigned NOT NULL DEFAULT 1,
    `status` enum('pending','confirmed','failed','refunded') NOT NULL DEFAULT 'pending',
    `block_number` bigint unsigned DEFAULT NULL,
    `block_timestamp` timestamp NULL DEFAULT NULL,
    `metadata` json DEFAULT NULL,
    `processed_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `payments_tenant_id_foreign` (`tenant_id`),
    KEY `payments_subscription_id_foreign` (`subscription_id`),
    KEY `payments_license_id_foreign` (`license_id`),
    KEY `payments_tx_hash_unique` (`tx_hash`),
    KEY `payments_status_index` (`status`),
    KEY `payments_network_index` (`network`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Devices (landlord)
CREATE TABLE IF NOT EXISTS `devices` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned NOT NULL,
    `license_id` bigint unsigned DEFAULT NULL,
    `device_id` varchar(255) NOT NULL,
    `device_name` varchar(255) DEFAULT NULL,
    `platform` varchar(100) DEFAULT NULL,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text DEFAULT NULL,
    `last_seen_at` timestamp NULL DEFAULT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `devices_tenant_device_unique` (`tenant_id`,`device_id`),
    KEY `devices_license_id_foreign` (`license_id`),
    KEY `devices_last_seen_index` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Logs (landlord)
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned DEFAULT NULL,
    `user_id` bigint unsigned DEFAULT NULL,
    `action` varchar(100) NOT NULL,
    `model_type` varchar(255) DEFAULT NULL,
    `model_id` varchar(255) DEFAULT NULL,
    `old_values` json DEFAULT NULL,
    `new_values` json DEFAULT NULL,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `audit_logs_tenant_id_index` (`tenant_id`),
    KEY `audit_logs_user_id_index` (`user_id`),
    KEY `audit_logs_action_index` (`action`),
    KEY `audit_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sync Logs (landlord)
CREATE TABLE IF NOT EXISTS `sync_logs` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tenant_id` bigint unsigned NOT NULL,
    `device_id` varchar(255) DEFAULT NULL,
    `direction` enum('push','pull','conflict') NOT NULL,
    `tables` json NOT NULL,
    `records_count` int unsigned NOT NULL DEFAULT 0,
    `status` enum('success','partial','failed') NOT NULL,
    `error_message` text DEFAULT NULL,
    `duration_ms` int unsigned DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `sync_logs_tenant_id_index` (`tenant_id`),
    KEY `sync_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings (landlord - global settings)
CREATE TABLE IF NOT EXISTS `settings` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `key` varchar(100) NOT NULL,
    `value` text DEFAULT NULL,
    `type` varchar(50) NOT NULL DEFAULT 'string',
    `group` varchar(50) DEFAULT NULL,
    `description` text DEFAULT NULL,
    `is_public` tinyint(1) NOT NULL DEFAULT 0,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default plans
INSERT INTO `plans` (`key`, `name`, `description`, `price_monthly_usdt`, `price_yearly_usdt`, `price_lifetime_usdt`, `features`, `limits`, `is_active`, `sort_order`) VALUES
('starter', 'Starter', 'للمتاجر الصغيرة', 10.00, 100.00, 0.00, '["منتجات غير محدودة", "عملاء غير محدودين", "دعم أساسي"]', '{"users": 2, "devices": 1, "api_calls_per_day": 10000}', 1, 1),
('professional', 'Professional', 'للمتاجر المتوسطة', 25.00, 250.00, 0.00, '["كل ميزات Starter", "تقارير متقدمة", "API كامل", "دعم أولوية"]', '{"users": 10, "devices": 3, "api_calls_per_day": 100000}', 1, 2),
('enterprise', 'Enterprise', 'للسلاسل والمتاجر الكبيرة', 99.00, 990.00, 0.00, '["كل ميزات Professional", "متعدد الفروع", "تكاملات مخصصة", "مدير حساب مخصص"]', '{"users": -1, "devices": -1, "api_calls_per_day": -1}', 1, 3);

-- Insert default settings
INSERT INTO `settings` (`key`, `value`, `type`, `group`, `description`, `is_public`) VALUES
('app_name', 'Market Manager', 'string', 'general', 'اسم التطبيق', 1),
('app_currency', 'USD', 'string', 'general', 'العملة الافتراضية', 1),
('app_timezone', 'UTC', 'string', 'general', 'المنطقة الزمنية', 1),
('tax_rate', '15', 'decimal', 'financial', 'نسبة الضريبة %', 1),
('usdt_trc20_wallet', '', 'string', 'payments', 'عنوان محفظة USDT TRC20', 0),
('usdt_erc20_wallet', '', 'string', 'payments', 'عنوان محفظة USDT ERC20', 0),
('usdt_min_confirmations_trc20', '1', 'integer', 'payments', 'تأكيدات TRC20 المطلوبة', 0),
('usdt_min_confirmations_erc20', '12', 'integer', 'payments', 'تأكيدات ERC20 المطلوبة', 0),
('trial_days', '3', 'integer', 'billing', 'أيام التجربة المجانية', 0),
('grace_period_days', '7', 'integer', 'billing', 'فترة سماح بعد انتهاء الاشتراك', 0);