-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema
-- Version: 2.0.0 with Categories and Payments
-- Date: 2024-12-19

-- =====================================================
-- PRODUCT CATEGORIES SYSTEM
-- =====================================================

-- Main product categories table
CREATE TABLE `product_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL UNIQUE,
  `description` text,
  `type` enum('b2b','b2c','both') NOT NULL DEFAULT 'both',
  `level` int(11) NOT NULL DEFAULT 1,
  `parent_id` bigint(20) UNSIGNED NULL,
  `icon` varchar(100) DEFAULT 'Package',
  `image_url` varchar(500) NULL,
  `is_active` boolean NOT NULL DEFAULT true,
  `commission_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `alibaba_category_id` varchar(100) NULL,
  `amazon_category_id` varchar(100) NULL,
  `meta_title` varchar(255) NULL,
  `meta_description` text NULL,
  `meta_keywords` text NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_type` (`type`),
  KEY `idx_level` (`level`),
  KEY `idx_active` (`is_active`),
  KEY `idx_slug` (`slug`),
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Category translations for multilingual support
CREATE TABLE `product_category_translations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `language_code` varchar(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `meta_title` varchar(255) NULL,
  `meta_description` text NULL,
  `meta_keywords` text NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_category_language` (`category_id`, `language_code`),
  KEY `idx_language` (`language_code`),
  CONSTRAINT `fk_category_translations` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Category attributes for flexible category properties
CREATE TABLE `category_attributes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `attribute_name` varchar(100) NOT NULL,
  `attribute_value` text,
  `attribute_type` enum('text','number','boolean','json') NOT NULL DEFAULT 'text',
  `is_required` boolean NOT NULL DEFAULT false,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category_attribute` (`category_id`, `attribute_name`),
  CONSTRAINT `fk_category_attributes` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product to category mapping (many-to-many)
CREATE TABLE `product_category_mappings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `is_primary` boolean NOT NULL DEFAULT false,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_product_category` (`product_id`, `category_id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_primary` (`is_primary`),
  CONSTRAINT `fk_product_category_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_product_category_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment method translations
CREATE TABLE `payment_method_translations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_method_id` bigint(20) UNSIGNED NOT NULL,
  `language_code` varchar(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `instructions` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_payment_language` (`payment_method_id`, `language_code`),
  KEY `idx_language` (`language_code`),
  CONSTRAINT `fk_payment_translations` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Country-specific payment method configurations
CREATE TABLE `payment_method_countries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_method_id` bigint(20) UNSIGNED NOT NULL,
  `country_code` varchar(3) NOT NULL,
  `is_enabled` boolean NOT NULL DEFAULT true,
  `local_name` varchar(255) NULL,
  `local_fee_percentage` decimal(5,2) NULL,
  `local_fee_fixed` decimal(10,2) NULL,
  `local_min_amount` decimal(15,2) NULL,
  `local_max_amount` decimal(15,2) NULL,
  `local_configuration` json NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_payment_country` (`payment_method_id`, `country_code`),
  KEY `idx_country` (`country_code`),
  KEY `idx_enabled` (`is_enabled`),
  CONSTRAINT `fk_payment_countries` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment transactions log
CREATE TABLE `payment_transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(100) NOT NULL UNIQUE,
  `order_id` bigint(20) UNSIGNED NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `payment_method_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `fee_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `net_amount` decimal(15,2) NOT NULL,
  `exchange_rate` decimal(10,6) NULL,
  `status` enum('pending','processing','completed','failed','cancelled','refunded','partially_refunded') NOT NULL DEFAULT 'pending',
  `gateway_transaction_id` varchar(255) NULL,
  `gateway_response` json NULL,
  `failure_reason` text NULL,
  `processed_at` timestamp NULL,
  `refunded_at` timestamp NULL,
  `refund_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `metadata` json NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_payment_method` (`payment_method_id`),
  KEY `idx_status` (`status`),
  KEY `idx_currency` (`currency`),
  KEY `idx_processed_at` (`processed_at`),
  CONSTRAINT `fk_payment_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payment_transactions_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Wise Business transfers
CREATE TABLE `wise_business_transfers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `transfer_id` varchar(100) NOT NULL UNIQUE,
  `wise_account_id` bigint(20) UNSIGNED NOT NULL,
  `payment_transaction_id` bigint(20) UNSIGNED NOT NULL,
  `source_currency` varchar(3) NOT NULL,
  `target_currency` varchar(3) NOT NULL,
  `source_amount` decimal(15,2) NOT NULL,
  `target_amount` decimal(15,2) NOT NULL,
  `exchange_rate` decimal(10,6) NOT NULL,
  `fee_amount` decimal(15,2) NOT NULL,
  `recipient_name` varchar(255) NOT NULL,
  `recipient_account` varchar(255) NOT NULL,
  `reference` varchar(255) NULL,
  `status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
  `wise_transfer_id` varchar(100) NULL,
  `wise_quote_id` varchar(100) NULL,
  `wise_response` json NULL,
  `estimated_delivery` timestamp NULL,
  `completed_at` timestamp NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transfer_id` (`transfer_id`),
  KEY `idx_wise_account` (`wise_account_id`),
  KEY `idx_payment_transaction` (`payment_transaction_id`),
  KEY `idx_status` (`status`),
  KEY `idx_wise_transfer` (`wise_transfer_id`),
  CONSTRAINT `fk_wise_transfers_account` FOREIGN KEY (`wise_account_id`) REFERENCES `wise_business_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wise_transfers_transaction` FOREIGN KEY (`payment_transaction_id`) REFERENCES `payment_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT CATEGORIES DATA
-- =====================================================

INSERT INTO `product_categories` (`id`, `name`, `slug`, `description`, `type`, `level`, `parent_id`, `icon`, `commission_rate`, `alibaba_category_id`, `amazon_category_id`, `sort_order`) VALUES
(1, 'Electronics & Technology', 'electronics-technology', 'Electronic devices, computers, and technology products', 'both', 1, NULL, 'Laptop', 3.50, 'electronics', 'electronics', 1),
(2, 'Fashion & Apparel', 'fashion-apparel', 'Clothing, shoes, accessories, and fashion items', 'both', 1, NULL, 'Shirt', 8.00, 'apparel', 'clothing', 2),
(3, 'Automotive & Transportation', 'automotive-transportation', 'Vehicles, parts, accessories, and transportation equipment', 'b2b', 1, NULL, 'Car', 2.50, 'automotive', 'automotive', 3),
(4, 'Home & Garden', 'home-garden', 'Furniture, home decor, garden supplies, and household items', 'both', 1, NULL, 'Home', 5.50, 'home-garden', 'home-garden', 4),
(5, 'Sports & Recreation', 'sports-recreation', 'Sports equipment, outdoor gear, and recreational products', 'both', 1, NULL, 'Dumbbell', 6.00, 'sports', 'sports', 5),
(6, 'Health & Beauty', 'health-beauty', 'Healthcare products, cosmetics, and personal care items', 'both', 1, NULL, 'Heart', 12.00, 'beauty', 'beauty', 6),
(7, 'Industrial & Manufacturing', 'industrial-manufacturing', 'Industrial equipment, machinery, and manufacturing supplies', 'b2b', 1, NULL, 'Wrench', 4.00, 'machinery', 'industrial', 7),
(8, 'Food & Beverages', 'food-beverages', 'Food products, beverages, and culinary supplies', 'both', 1, NULL, 'Coffee', 15.00, 'food', 'grocery', 8),
(9, 'Baby & Kids', 'baby-kids', 'Baby products, toys, and children items', 'both', 1, NULL, 'Baby', 10.00, 'toys', 'baby', 9),
(10, 'Books & Education', 'books-education', 'Books, educational materials, and learning resources', 'both', 1, NULL, 'Book', 7.00, 'education', 'books', 10);

-- Electronics subcategories
INSERT INTO `product_categories` (`name`, `slug`, `description`, `type`, `level`, `parent_id`, `icon`, `commission_rate`, `sort_order`) VALUES
('Smartphones & Tablets', 'smartphones-tablets', 'Mobile phones, tablets, and accessories', 'both', 2, 1, 'Smartphone', 3.50, 1),
('Computers & Laptops', 'computers-laptops', 'Desktop computers, laptops, and components', 'both', 2, 1, 'Laptop', 3.50, 2),
('Audio & Video', 'audio-video', 'Headphones, speakers, cameras, and entertainment devices', 'both', 2, 1, 'Headphones', 3.50, 3),
('Gaming', 'gaming', 'Gaming consoles, games, and gaming accessories', 'b2c', 2, 1, 'Gamepad2', 3.50, 4),
('Smart Home', 'smart-home', 'Smart home devices, IoT products, and automation', 'both', 2, 1, 'Zap', 3.50, 5);

-- Fashion subcategories
INSERT INTO `product_categories` (`name`, `slug`, `description`, `type`, `level`, `parent_id`, `icon`, `commission_rate`, `sort_order`) VALUES
('Men\'s Clothing', 'mens-clothing', 'Men\'s apparel and fashion items', 'both', 2, 2, 'Shirt', 8.00, 1),
('Women\'s Clothing', 'womens-clothing', 'Women\'s apparel and fashion items', 'both', 2, 2, 'Shirt', 8.00, 2),
('Shoes & Footwear', 'shoes-footwear', 'All types of shoes and footwear', 'both', 2, 2, 'Shirt', 8.00, 3),
('Accessories', 'accessories', 'Fashion accessories, jewelry, and bags', 'both', 2, 2, 'Watch', 8.00, 4);

-- =====================================================
-- INDEXES AND OPTIMIZATIONS
-- =====================================================

-- Additional indexes for performance
CREATE INDEX `idx_categories_type_active` ON `product_categories` (`type`, `is_active`);
CREATE INDEX `idx_categories_parent_sort` ON `product_categories` (`parent_id`, `sort_order`);
CREATE INDEX `idx_payment_transactions_user_status` ON `payment_transactions` (`user_id`, `status`);
CREATE INDEX `idx_payment_transactions_created` ON `payment_transactions` (`created_at`);

-- =====================================================
-- VIEWS FOR EASY DATA ACCESS
-- =====================================================

-- Category hierarchy view
CREATE VIEW `v_category_hierarchy` AS
SELECT 
    c.id,
    c.name,
    c.slug,
    c.type,
    c.level,
    c.parent_id,
    p.name as parent_name,
    c.icon,
    c.commission_rate,
    c.is_active,
    (SELECT COUNT(*) FROM product_category_mappings pcm WHERE pcm.category_id = c.id) as product_count,
    (SELECT COUNT(*) FROM product_categories sc WHERE sc.parent_id = c.id) as subcategory_count
FROM product_categories c
LEFT JOIN product_categories p ON c.parent_id = p.id
ORDER BY c.level, c.sort_order;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

DELIMITER //

-- Get category tree with product counts
CREATE PROCEDURE `GetCategoryTree`(IN parent_category_id BIGINT)
BEGIN
    WITH RECURSIVE category_tree AS (
        SELECT id, name, slug, type, level, parent_id, icon, commission_rate, is_active, sort_order, 0 as depth
        FROM product_categories 
        WHERE parent_id = parent_category_id OR (parent_category_id IS NULL AND parent_id IS NULL)
        
        UNION ALL
        
        SELECT c.id, c.name, c.slug, c.type, c.level, c.parent_id, c.icon, c.commission_rate, c.is_active, c.sort_order, ct.depth + 1
        FROM product_categories c
        INNER JOIN category_tree ct ON c.parent_id = ct.id
        WHERE ct.depth < 5
    )
    SELECT 
        ct.*,
        (SELECT COUNT(*) FROM product_category_mappings pcm WHERE pcm.category_id = ct.id) as product_count
    FROM category_tree ct
    ORDER BY ct.depth, ct.sort_order;
END //

DELIMITER ;

-- =====================================================
-- TRIGGERS FOR DATA INTEGRITY
-- =====================================================

DELIMITER //

-- Update category product count when mapping changes
CREATE TRIGGER `update_category_product_count_insert` 
AFTER INSERT ON `product_category_mappings`
FOR EACH ROW
BEGIN
    UPDATE product_categories 
    SET updated_at = CURRENT_TIMESTAMP 
    WHERE id = NEW.category_id;
END //

CREATE TRIGGER `update_category_product_count_delete` 
AFTER DELETE ON `product_category_mappings`
FOR EACH ROW
BEGIN
    UPDATE product_categories 
    SET updated_at = CURRENT_TIMESTAMP 
    WHERE id = OLD.category_id;
END //

-- Auto-generate category slug from name
CREATE TRIGGER `generate_category_slug` 
BEFORE INSERT ON `product_categories`
FOR EACH ROW
BEGIN
    IF NEW.slug IS NULL OR NEW.slug = '' THEN
        SET NEW.slug = LOWER(REPLACE(REPLACE(REPLACE(NEW.name, ' ', '-'), '&', 'and'), '--', '-'));
    END IF;
    END IF;
END //

DELIMITER ;

-- =====================================================
-- SAMPLE DATA FOR TESTING
-- =====================================================

-- Insert sample category translations
INSERT INTO `product_category_translations` (`category_id`, `language_code`, `name`, `description`) VALUES
(1, 'de', 'Elektronik & Technologie', 'Elektronische Geräte, Computer und Technologieprodukte'),
(1, 'fr', 'Électronique et Technologie', 'Appareils électroniques, ordinateurs et produits technologiques'),
(1, 'es', 'Electrónica y Tecnología', 'Dispositivos electrónicos, computadoras y productos tecnológicos'),
(2, 'de', 'Mode & Bekleidung', 'Kleidung, Schuhe, Accessoires und Modeartikel'),
(2, 'fr', 'Mode et Vêtements', 'Vêtements, chaussures, accessoires et articles de mode'),
(2, 'es', 'Moda y Ropa', 'Ropa, zapatos, accesorios y artículos de moda');

-- Insert sample payment method translations
INSERT INTO `payment_method_translations` (`payment_method_id`, `language_code`, `name`, `description`) VALUES
(1, 'de', 'Wise Business', 'Internationale Geldtransfers mit echten Wechselkursen'),
(1, 'fr', 'Wise Business', 'Transferts d\'argent internationaux avec des taux de change réels'),
(1, 'es', 'Wise Business', 'Transferencias internacionales de dinero con tipos de cambio reales'),
(2, 'de', 'PayPal Business', 'Globale digitale Zahlungsplattform'),
(2, 'fr', 'PayPal Business', 'Plateforme de paiement numérique mondiale'),
(2, 'es', 'PayPal Business', 'Plataforma de pago digital global');

-- =====================================================
-- FINAL OPTIMIZATIONS
-- =====================================================

-- Analyze tables for better query performance
ANALYZE TABLE product_categories;
ANALYZE TABLE product_category_translations;
ANALYZE TABLE product_category_mappings;
ANALYZE TABLE payment_method_translations;
ANALYZE TABLE payment_transactions;

-- Set table statistics for optimizer
OPTIMIZE TABLE product_categories;
OPTIMIZE TABLE payment_transactions;

-- =====================================================
-- END OF SCHEMA
-- =====================================================
