-- Smart Word Replacer License Server Database Schema
-- Version: 1.0.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Lisanslar Tablosu
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `licenses` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_key` varchar(32) NOT NULL,
    `license_type` varchar(30) NOT NULL DEFAULT 'starter',
    `status` enum('active','expired','suspended','revoked') NOT NULL DEFAULT 'active',
    `max_sites` int(11) NOT NULL DEFAULT 1,
    
    -- Müşteri Bilgileri
    `customer_name` varchar(255) DEFAULT NULL,
    `customer_email` varchar(255) NOT NULL,
    `customer_phone` varchar(50) DEFAULT NULL,
    
    -- Ödeme Bilgileri
    `payment_id` varchar(100) DEFAULT NULL,
    `payment_method` varchar(50) DEFAULT 'paynet',
    `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
    `currency` varchar(3) NOT NULL DEFAULT 'USD',
    
    -- Tarihler
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` datetime DEFAULT NULL,
    `activated_at` datetime DEFAULT NULL,
    `last_verified_at` datetime DEFAULT NULL,
    
    -- Notlar
    `notes` text DEFAULT NULL,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `license_key` (`license_key`),
    KEY `customer_email` (`customer_email`),
    KEY `status` (`status`),
    KEY `license_type` (`license_type`),
    KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Site Aktivasyonları Tablosu
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `activations` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_id` bigint(20) UNSIGNED NOT NULL,
    `domain` varchar(255) NOT NULL,
    `domain_hash` varchar(64) NOT NULL,
    `site_name` varchar(255) DEFAULT NULL,
    `ip_address` varchar(45) DEFAULT NULL,
    `status` enum('active','deactivated') NOT NULL DEFAULT 'active',
    `activated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deactivated_at` datetime DEFAULT NULL,
    `last_check_at` datetime DEFAULT NULL,
    `plugin_version` varchar(20) DEFAULT NULL,
    `wp_version` varchar(20) DEFAULT NULL,
    `php_version` varchar(20) DEFAULT NULL,
    
    PRIMARY KEY (`id`),
    KEY `license_id` (`license_id`),
    KEY `domain` (`domain`),
    KEY `domain_hash` (`domain_hash`),
    KEY `status` (`status`),
    CONSTRAINT `fk_activations_license` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- API İstekleri Log Tablosu
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_id` bigint(20) UNSIGNED DEFAULT NULL,
    `endpoint` varchar(50) NOT NULL,
    `domain` varchar(255) DEFAULT NULL,
    `ip_address` varchar(45) NOT NULL,
    `request_data` text DEFAULT NULL,
    `response_status` varchar(20) NOT NULL,
    `response_message` varchar(255) DEFAULT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `license_id` (`license_id`),
    KEY `endpoint` (`endpoint`),
    KEY `ip_address` (`ip_address`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Ödeme İşlemleri Tablosu
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_id` bigint(20) UNSIGNED DEFAULT NULL,
    `transaction_id` varchar(100) NOT NULL,
    `payment_method` varchar(50) NOT NULL DEFAULT 'paynet',
    `amount` decimal(10,2) NOT NULL,
    `currency` varchar(3) NOT NULL DEFAULT 'USD',
    `status` enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
    `customer_email` varchar(255) NOT NULL,
    `customer_name` varchar(255) DEFAULT NULL,
    `product_type` varchar(30) NOT NULL,
    `raw_response` text DEFAULT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` datetime DEFAULT NULL,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `transaction_id` (`transaction_id`),
    KEY `license_id` (`license_id`),
    KEY `status` (`status`),
    KEY `customer_email` (`customer_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Ayarlar Tablosu
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` varchar(100) NOT NULL,
    `setting_value` text DEFAULT NULL,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Varsayılan Ayarlar
-- --------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'Smart Word Replacer License Server'),
('admin_email', 'admin@zefre.net'),
('paynet_enabled', '1'),
('paynet_secret_key', ''),
('paynet_public_key', ''),
('paynet_test_mode', '1'),
('grace_period_hours', '6'),
('cache_duration_hours', '24');

COMMIT;
