CREATE DATABASE IF NOT EXISTS gestimmo_licenses CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gestimmo_licenses;

-- ============================================
-- TABLE: licenses (Licences)
-- ============================================
CREATE TABLE licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(19) UNIQUE NOT NULL COMMENT 'Format: XXXX-XXXX-XXXX-XXXX',
    customer_id INT NOT NULL,
    
    -- Informations de licence
    plan ENUM('STARTER', 'PRO', 'ENTERPRISE', 'LIFETIME') NOT NULL DEFAULT 'STARTER',
    status ENUM('active', 'expired', 'suspended', 'revoked') NOT NULL DEFAULT 'active',
    
    -- Domaine autorisé
    domain VARCHAR(255) NOT NULL COMMENT 'Domaine du client',
    ip_address VARCHAR(45) NULL COMMENT 'IP du serveur client',
    
    -- Limites du plan
    max_users INT DEFAULT 5 COMMENT 'Nombre max d\'utilisateurs',
    max_properties INT DEFAULT 50 COMMENT 'Nombre max de propriétés',
    
    -- Dates
    issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL COMMENT 'NULL pour LIFETIME',
    last_check DATETIME NULL COMMENT 'Dernière vérification',
    last_ip VARCHAR(45) NULL COMMENT 'Dernière IP de check',
    
    -- Statistiques
    check_count INT DEFAULT 0 COMMENT 'Nombre de vérifications',
    install_count INT DEFAULT 0 COMMENT 'Nombre d\'installations',
    
    -- Métadonnées
    metadata JSON NULL COMMENT 'Données supplémentaires',
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_license_key (license_key),
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_domain (domain),
    INDEX idx_expires (expires_at),
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: customers (Clients)
-- ============================================
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Informations personnelles
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    company_name VARCHAR(255) NULL,
    
    -- Contact
    phone VARCHAR(20) NULL,
    address TEXT NULL,
    city VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    
    -- Compte
    password_hash VARCHAR(255) NULL COMMENT 'Pour espace client',
    status ENUM('active', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
    
    -- Paiement
    stripe_customer_id VARCHAR(255) NULL,
    paypal_email VARCHAR(255) NULL,
    
    -- Statistiques
    total_licenses INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    
    -- Métadonnées
    notes TEXT NULL,
    metadata JSON NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: license_checks (Historique des vérifications)
-- ============================================
CREATE TABLE license_checks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    
    -- Informations de la requête
    check_type ENUM('install', 'validate', 'cron', 'manual') NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    domain VARCHAR(255) NOT NULL,
    
    -- Résultat
    result ENUM('valid', 'invalid', 'expired', 'suspended', 'error') NOT NULL,
    error_message TEXT NULL,
    
    -- Données système client
    php_version VARCHAR(20) NULL,
    mysql_version VARCHAR(20) NULL,
    server_software VARCHAR(255) NULL,
    
    -- Métadonnées
    request_data JSON NULL,
    response_data JSON NULL,
    
    checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_license (license_id),
    INDEX idx_date (checked_at),
    INDEX idx_result (result),
    
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: transactions (Historique des paiements)
-- ============================================
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    license_id INT NULL,
    
    -- Informations de paiement
    transaction_id VARCHAR(255) UNIQUE NOT NULL COMMENT 'ID Stripe/PayPal',
    payment_method ENUM('stripe', 'paypal', 'bank_transfer', 'manual') NOT NULL,
    
    -- Montants
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    tax DECIMAL(10,2) DEFAULT 0.00,
    
    -- Statut
    status ENUM('pending', 'completed', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    
    -- Détails
    plan ENUM('STARTER', 'PRO', 'ENTERPRISE', 'LIFETIME') NOT NULL,
    duration INT NULL COMMENT 'Durée en mois (NULL pour LIFETIME)',
    
    -- Métadonnées
    metadata JSON NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_customer (customer_id),
    INDEX idx_transaction (transaction_id),
    INDEX idx_status (status),
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: admin_users (Administrateurs)
-- ============================================
CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    
    role ENUM('super_admin', 'admin', 'support') NOT NULL DEFAULT 'admin',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    
    last_login DATETIME NULL,
    last_ip VARCHAR(45) NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: activity_logs (Logs d'activité)
-- ============================================
CREATE TABLE activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    user_type ENUM('admin', 'customer', 'system') NOT NULL,
    user_id INT NULL,
    
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NULL COMMENT 'license, customer, etc.',
    entity_id INT NULL,
    
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_type, user_id),
    INDEX idx_action (action),
    INDEX idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: api_keys (Clés API pour intégrations)
-- ============================================
CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_hash VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL COMMENT 'Nom descriptif',
    
    permissions JSON NOT NULL COMMENT 'Permissions accordées',
    
    status ENUM('active', 'inactive', 'revoked') NOT NULL DEFAULT 'active',
    
    last_used DATETIME NULL,
    expires_at DATETIME NULL,
    
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_key (key_hash),
    INDEX idx_status (status),
    
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DONNÉES INITIALES
-- ============================================

-- Admin par défaut (password: Admin@2024)
INSERT INTO admin_users (username, email, password_hash, role) VALUES
('admin', 'admin@votresite.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Plans tarifaires (pour référence)
CREATE TABLE pricing_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name ENUM('STARTER', 'PRO', 'ENTERPRISE', 'LIFETIME') UNIQUE NOT NULL,
    
    price_monthly DECIMAL(10,2) NULL,
    price_yearly DECIMAL(10,2) NULL,
    price_lifetime DECIMAL(10,2) NULL,
    
    max_users INT NOT NULL,
    max_properties INT NOT NULL,
    
    features JSON NOT NULL,
    
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO pricing_plans (plan_name, price_monthly, price_yearly, price_lifetime, max_users, max_properties, features) VALUES
('STARTER', 29.00, 290.00, NULL, 5, 50, '["Gestion propriétés", "Gestion locataires", "Suivi loyers", "Support email"]'),
('PRO', 99.00, 990.00, NULL, 20, 200, '["Tout STARTER", "Multi-utilisateurs", "Rapports avancés", "Support prioritaire", "Personnalisation logo"]'),
('ENTERPRISE', 299.00, 2990.00, NULL, 999999, 999999, '["Tout PRO", "Utilisateurs illimités", "Propriétés illimitées", "Support 24/7", "White-label", "API personnalisée"]'),
('LIFETIME', NULL, NULL, 1999.00, 999999, 999999, '["Tous avantages ENTERPRISE", "Mises à jour à vie", "Support à vie", "Installation dédiée"]');

-- Table des codes promo
CREATE TABLE promo_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    type ENUM('percentage', 'fixed', 'trial_extension', 'free_upgrade') NOT NULL,
    value DECIMAL(10,2) NOT NULL COMMENT 'Pourcentage ou montant',
    currency VARCHAR(3) DEFAULT 'EUR',
    plan_restriction VARCHAR(50) DEFAULT NULL COMMENT 'Plan spécifique ou NULL pour tous',
    max_uses INT DEFAULT NULL COMMENT 'Nombre max d\'utilisations',
    used_count INT DEFAULT 0,
    valid_from DATETIME DEFAULT CURRENT_TIMESTAMP,
    valid_until DATETIME DEFAULT NULL,
    created_by INT DEFAULT NULL,
    status ENUM('active', 'inactive', 'expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_status (status),
    INDEX idx_valid (valid_from, valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des transactions de paiement
CREATE TABLE payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id VARCHAR(255) UNIQUE NOT NULL COMMENT 'ID du provider',
    provider ENUM('fedapay', 'stripe', 'paypal') NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    license_id INT DEFAULT NULL,
    customer_email VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT NULL COMMENT 'mobile_money, card, paypal, etc.',
    promo_code VARCHAR(50) DEFAULT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(10,2) NOT NULL COMMENT 'Montant après réduction',
    metadata JSON DEFAULT NULL,
    webhook_payload TEXT DEFAULT NULL,
    processed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL,
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_provider (provider),
    INDEX idx_status (status),
    INDEX idx_email (customer_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des essais gratuits
CREATE TABLE trial_licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    started_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    converted_at DATETIME DEFAULT NULL COMMENT 'Date de conversion en payant',
    converted_to_plan VARCHAR(50) DEFAULT NULL,
    reminders_sent JSON DEFAULT NULL COMMENT 'Rappels envoyés',
    status ENUM('active', 'expired', 'converted', 'canceled') DEFAULT 'active',
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des renouvellements automatiques
CREATE TABLE auto_renewals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    payment_method_id VARCHAR(255) DEFAULT NULL COMMENT 'ID méthode paiement chez provider',
    provider ENUM('fedapay', 'stripe', 'paypal') NOT NULL,
    next_renewal_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    retry_count INT DEFAULT 0,
    last_attempt_at DATETIME DEFAULT NULL,
    last_failure_reason TEXT DEFAULT NULL,
    status ENUM('active', 'paused', 'failed', 'canceled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    INDEX idx_next_renewal (next_renewal_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des notifications SMS
CREATE TABLE sms_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient VARCHAR(20) NOT NULL COMMENT 'Numéro de téléphone',
    message TEXT NOT NULL,
    provider ENUM('termii', 'vonage') NOT NULL,
    type ENUM('admin_sale', 'customer_license', 'trial_reminder', 'renewal_reminder') NOT NULL,
    status ENUM('pending', 'sent', 'failed', 'delivered') DEFAULT 'pending',
    provider_message_id VARCHAR(255) DEFAULT NULL,
    cost DECIMAL(10,4) DEFAULT 0 COMMENT 'Coût du SMS',
    error_message TEXT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    delivered_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_recipient (recipient),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des webhooks reçus (pour débogage)
CREATE TABLE webhook_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(255) UNIQUE NOT NULL,
    provider ENUM('fedapay', 'stripe', 'paypal') NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload TEXT NOT NULL,
    signature VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    processed BOOLEAN DEFAULT FALSE,
    processing_error TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME DEFAULT NULL,
    INDEX idx_event_id (event_id),
    INDEX idx_processed (processed),
    INDEX idx_provider (provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ajouter des codes promo d'exemple
INSERT INTO promo_codes (code, type, value, max_uses, valid_until) VALUES
('LAUNCH50', 'percentage', 50, 100, DATE_ADD(NOW(), INTERVAL 30 DAY)),
('SAVE10EUR', 'fixed', 10, NULL, DATE_ADD(NOW(), INTERVAL 60 DAY)),
('TRIAL30', 'trial_extension', 30, 50, DATE_ADD(NOW(), INTERVAL 90 DAY)),
('UPGRADE2PRO', 'free_upgrade', 0, 20, DATE_ADD(NOW(), INTERVAL 30 DAY));
-- Fin du fichier licenses.sql