-- ============================================================================
-- MergenFlow Core Schema Migration 001
-- Engine: MySQL 8.4 LTS InnoDB | Charset: utf8mb4_unicode_ci
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Tenants (İşletme / Müşteri Hesapları)
CREATE TABLE IF NOT EXISTS `tenants` (
    `id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `subdomain` VARCHAR(64) NOT NULL,
    `company_title` VARCHAR(255) NULL,
    `tax_number` VARCHAR(32) NULL,
    `tax_office` VARCHAR(128) NULL,
    `phone` VARCHAR(32) NULL,
    `status` ENUM('active', 'suspended', 'pending') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenants_subdomain` (`subdomain`),
    KEY `idx_tenants_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Users (Kullanıcılar)
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(191) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(64) NOT NULL,
    `last_name` VARCHAR(64) NOT NULL,
    `phone` VARCHAR(32) NULL,
    `is_super_admin` TINYINT(1) NOT NULL DEFAULT 0,
    `status` ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
    `mfa_secret` VARCHAR(64) NULL,
    `remember_token` VARCHAR(128) NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_users_email` (`email`),
    KEY `idx_users_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tenant Users (Çok Kiracılı Rol İlişkisi)
CREATE TABLE IF NOT EXISTS `tenant_users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `role` ENUM('owner', 'admin', 'operator', 'viewer') NOT NULL DEFAULT 'operator',
    `status` ENUM('active', 'invited', 'suspended') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_user` (`tenant_id`, `user_id`),
    KEY `idx_tenant_users_user` (`user_id`),
    CONSTRAINT `fk_tu_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_tu_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Subscription Plans (Abonelik Paketleri)
CREATE TABLE IF NOT EXISTS `subscription_plans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `slug` VARCHAR(64) NOT NULL,
    `monthly_email_quota` INT UNSIGNED NOT NULL DEFAULT 1000,
    `monthly_whatsapp_quota` INT UNSIGNED NOT NULL DEFAULT 100,
    `max_stores` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `max_team_members` TINYINT UNSIGNED NOT NULL DEFAULT 2,
    `max_automations` TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `price_monthly` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `price_yearly` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_plans_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Subscriptions (Müşteri Abonelikleri)
CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `plan_id` INT UNSIGNED NOT NULL,
    `status` ENUM('active', 'past_due', 'canceled', 'trialing') NOT NULL DEFAULT 'trialing',
    `current_period_start` DATE NOT NULL,
    `current_period_end` DATE NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_subscription` (`tenant_id`),
    CONSTRAINT `fk_sub_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sub_plan` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Usage Records (Aylık Kullanım ve Kota Takibi)
CREATE TABLE IF NOT EXISTS `usage_records` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `billing_period` VARCHAR(7) NOT NULL, -- YYYY-MM
    `emails_sent` INT UNSIGNED NOT NULL DEFAULT 0,
    `whatsapp_sent` INT UNSIGNED NOT NULL DEFAULT 0,
    `extra_charges` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_usage_tenant_period` (`tenant_id`, `billing_period`),
    CONSTRAINT `fk_usage_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Stores (E-ticaret Mağazaları - Shopify / ikas / Ticimax)
CREATE TABLE IF NOT EXISTS `stores` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `platform` ENUM('shopify', 'ikas', 'ticimax') NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `domain` VARCHAR(191) NOT NULL,
    `api_key_encrypted` TEXT NULL,
    `api_secret_encrypted` TEXT NULL,
    `access_token_encrypted` TEXT NULL,
    `webhook_secret` VARCHAR(255) NULL,
    `status` ENUM('connected', 'error', 'disconnected') NOT NULL DEFAULT 'connected',
    `last_sync_at` DATETIME NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_stores_tenant` (`tenant_id`),
    CONSTRAINT `fk_stores_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Sender Domains & Identities (Amazon SES)
CREATE TABLE IF NOT EXISTS `sender_domains` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `domain` VARCHAR(191) NOT NULL,
    `ses_identity_arn` VARCHAR(255) NULL,
    `dkim_tokens` JSON NULL,
    `verification_status` ENUM('pending', 'verified', 'failed') NOT NULL DEFAULT 'pending',
    `dkim_status` ENUM('pending', 'success', 'failed') NOT NULL DEFAULT 'pending',
    `dmarc_status` ENUM('pending', 'success', 'missing') NOT NULL DEFAULT 'pending',
    `dns_records` JSON NULL,
    `verified_at` DATETIME NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_domain` (`tenant_id`, `domain`),
    CONSTRAINT `fk_sd_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sender_identities` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `domain_id` INT UNSIGNED NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `display_name` VARCHAR(128) NOT NULL,
    `is_default` TINYINT(1) NOT NULL DEFAULT 0,
    `status` ENUM('active', 'pending', 'rejected') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_sender_email` (`tenant_id`, `email`),
    CONSTRAINT `fk_si_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_si_domain` FOREIGN KEY (`domain_id`) REFERENCES `sender_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. WhatsApp Accounts (Meta WhatsApp Cloud API)
CREATE TABLE IF NOT EXISTS `whatsapp_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `waba_id` VARCHAR(64) NOT NULL,
    `phone_number_id` VARCHAR(64) NOT NULL,
    `display_phone_number` VARCHAR(32) NOT NULL,
    `quality_rating` VARCHAR(32) DEFAULT 'UNKNOWN',
    `access_token_encrypted` TEXT NOT NULL,
    `status` ENUM('active', 'restricted', 'disconnected') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_waba_phone` (`tenant_id`, `phone_number_id`),
    CONSTRAINT `fk_wa_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Contacts & Suppressions (Kişiler, İzinler ve Kara Liste)
CREATE TABLE IF NOT EXISTS `contacts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `first_name` VARCHAR(64) NULL,
    `last_name` VARCHAR(64) NULL,
    `email` VARCHAR(191) NULL,
    `phone` VARCHAR(32) NULL,
    `email_consent` ENUM('granted', 'revoked', 'unknown') NOT NULL DEFAULT 'unknown',
    `whatsapp_consent` ENUM('granted', 'revoked', 'unknown') NOT NULL DEFAULT 'unknown',
    `consent_updated_at` DATETIME NULL,
    `status` ENUM('active', 'unsubscribed', 'bounced') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_contact_email` (`tenant_id`, `email`),
    KEY `idx_tenant_contact_phone` (`tenant_id`, `phone`),
    CONSTRAINT `fk_contacts_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_suppressions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `recipient` VARCHAR(191) NOT NULL,
    `channel` ENUM('email', 'whatsapp') NOT NULL,
    `reason` ENUM('hard_bounce', 'complaint', 'manual', 'opt_out') NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tenant_suppression` (`tenant_id`, `recipient`, `channel`),
    CONSTRAINT `fk_suppress_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Templates & Automations
CREATE TABLE IF NOT EXISTS `message_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `channel` ENUM('email', 'whatsapp') NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `subject` VARCHAR(255) NULL,
    `content_html` MEDIUMTEXT NULL,
    `content_plain` TEXT NULL,
    `meta_template_id` VARCHAR(64) NULL,
    `meta_status` ENUM('APPROVED', 'REJECTED', 'PENDING', 'PAUSED') NULL,
    `meta_category` VARCHAR(32) NULL,
    `language` VARCHAR(8) NOT NULL DEFAULT 'tr',
    `variables_schema` JSON NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_templates_tenant_channel` (`tenant_id`, `channel`),
    CONSTRAINT `fk_tpl_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `automations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `store_id` INT UNSIGNED NULL,
    `name` VARCHAR(191) NOT NULL,
    `trigger_event` VARCHAR(64) NOT NULL, -- order.created, cart.abandoned vb.
    `conditions` JSON NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_automations_tenant_trigger` (`tenant_id`, `trigger_event`),
    CONSTRAINT `fk_auto_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_auto_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `automation_steps` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `automation_id` INT UNSIGNED NOT NULL,
    `step_order` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `action_type` ENUM('send_email', 'send_whatsapp', 'delay') NOT NULL,
    `delay_minutes` INT UNSIGNED NOT NULL DEFAULT 0,
    `template_id` INT UNSIGNED NULL,
    `config` JSON NULL,
    PRIMARY KEY (`id`),
    KEY `idx_steps_automation` (`automation_id`, `step_order`),
    CONSTRAINT `fk_steps_auto` FOREIGN KEY (`automation_id`) REFERENCES `automations` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_steps_tpl` FOREIGN KEY (`template_id`) REFERENCES `message_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Outbound Messages & Reporting
CREATE TABLE IF NOT EXISTS `outbound_messages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NOT NULL,
    `channel` ENUM('email', 'whatsapp') NOT NULL,
    `sender_identity` VARCHAR(191) NOT NULL,
    `recipient` VARCHAR(191) NOT NULL,
    `subject` VARCHAR(255) NULL,
    `status` ENUM('queued', 'submitted', 'delivered', 'failed', 'bounced', 'complained') NOT NULL DEFAULT 'queued',
    `provider_message_id` VARCHAR(191) NULL,
    `error_code` VARCHAR(64) NULL,
    `error_message` TEXT NULL,
    `sent_at` DATETIME NULL,
    `delivered_at` DATETIME NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_outbound_tenant_status` (`tenant_id`, `status`),
    KEY `idx_outbound_created` (`created_at`),
    CONSTRAINT `fk_om_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Job Queue (MySQL 8.4 Skip Locked Worker Queue)
CREATE TABLE IF NOT EXISTS `job_queue` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(64) NOT NULL DEFAULT 'default',
    `job_class` VARCHAR(255) NOT NULL,
    `payload` JSON NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `max_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 3,
    `reserved_at` INT UNSIGNED NULL,
    `reserved_by` VARCHAR(128) NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_queue_claim` (`queue`, `reserved_at`, `available_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(64) NOT NULL,
    `job_class` VARCHAR(255) NOT NULL,
    `payload` JSON NOT NULL,
    `exception_message` TEXT NOT NULL,
    `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Audit Logs & System Settings
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` VARCHAR(36) NULL,
    `user_id` INT UNSIGNED NULL,
    `action` VARCHAR(64) NOT NULL,
    `resource_type` VARCHAR(64) NOT NULL,
    `resource_id` VARCHAR(64) NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `details` JSON NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_tenant_action` (`tenant_id`, `action`),
    KEY `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `system_settings` (
    `key_name` VARCHAR(128) NOT NULL,
    `value_text` TEXT NULL,
    `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;