-- AUTO TESTER - Pricing System Migration
-- Created: 2026-04-22
-- Description: Complete pricing management system for all services

-- ============================================
-- 1. Services Table
-- ============================================
CREATE TABLE IF NOT EXISTS `services` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_code` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Unique service identifier',
    `service_name_ar` VARCHAR(255) NOT NULL COMMENT 'Service name in Arabic',
    `service_name_en` VARCHAR(255) NOT NULL COMMENT 'Service name in English',
    `service_description_ar` TEXT COMMENT 'Service description in Arabic',
    `service_description_en` TEXT COMMENT 'Service description in English',
    `service_category` ENUM('inspection', 'test', 'certification', 'consulting', 'training', 'other') NOT NULL DEFAULT 'inspection',
    `service_type` ENUM('standard', 'premium', 'express', 'mobile') NOT NULL DEFAULT 'standard',
    
    -- Service details
    `duration_minutes` INT DEFAULT 60 COMMENT 'Expected duration in minutes',
    `inspection_points` INT DEFAULT 0 COMMENT 'Number of inspection points',
    `requires_appointment` BOOLEAN DEFAULT TRUE,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_featured` BOOLEAN DEFAULT FALSE,
    `sort_order` INT DEFAULT 0,
    
    -- Service requirements
    `vehicle_requirements` JSON COMMENT 'Vehicle type requirements',
    `document_requirements` JSON COMMENT 'Required documents',
    `location_requirements` ENUM('center_only', 'mobile_only', 'both') DEFAULT 'center_only',
    
    -- Service metadata
    `icon_class` VARCHAR(100) COMMENT 'Font Awesome icon class',
    `image_url` VARCHAR(500) COMMENT 'Service image URL',
    `tags` JSON COMMENT 'Service tags for search',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_service_code` (`service_code`),
    INDEX `idx_category` (`service_category`),
    INDEX `idx_service_type` (`service_type`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Service Pricing Table
-- ============================================
CREATE TABLE IF NOT EXISTS `service_pricing` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_id` INT NOT NULL,
    `pricing_type` ENUM('base_price', 'vehicle_type', 'additional_service', 'discount', 'surcharge') NOT NULL,
    
    -- Price details
    `price_amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'EGP',
    `pricing_unit` ENUM('per_service', 'per_point', 'per_hour', 'per_km', 'flat_rate') DEFAULT 'per_service',
    
    -- Applicability conditions
    `vehicle_type` ENUM('sedan', 'suv', 'truck', 'motorcycle', 'bus', 'all') DEFAULT 'all',
    `vehicle_age_min` INT DEFAULT 0 COMMENT 'Minimum vehicle age in years',
    `vehicle_age_max` INT DEFAULT 0 COMMENT 'Maximum vehicle age in years (0 = no limit)',
    `engine_size_min` DECIMAL(4,1) DEFAULT 0 COMMENT 'Minimum engine size in liters',
    `engine_size_max` DECIMAL(4,1) DEFAULT 0 COMMENT 'Maximum engine size in liters (0 = no limit)',
    `location_type` ENUM('center', 'mobile', 'online') DEFAULT 'center',
    `customer_type` ENUM('individual', 'corporate', 'government', 'all') DEFAULT 'all',
    
    -- Pricing conditions
    `min_quantity` INT DEFAULT 1 COMMENT 'Minimum quantity for this price',
    `max_quantity` INT DEFAULT 1 COMMENT 'Maximum quantity for this price (0 = no limit)',
    `valid_from` DATE DEFAULT CURRENT_DATE,
    `valid_until` DATE DEFAULT NULL COMMENT 'NULL means no end date',
    `is_active` BOOLEAN DEFAULT TRUE,
    
    -- Additional pricing details
    `price_description_ar` VARCHAR(255) COMMENT 'Price description in Arabic',
    `price_description_en` VARCHAR(255) COMMENT 'Price description in English',
    `price_formula` VARCHAR(500) COMMENT 'Price calculation formula if applicable',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by` INT COMMENT 'User who created this price',
    `updated_by` INT COMMENT 'User who last updated this price',
    
    FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_service_id` (`service_id`),
    INDEX `idx_pricing_type` (`pricing_type`),
    INDEX `idx_vehicle_type` (`vehicle_type`),
    INDEX `idx_validity` (`valid_from`, `valid_until`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Pricing History Table
-- ============================================
CREATE TABLE IF NOT EXISTS `pricing_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_id` INT NOT NULL,
    `pricing_id` INT NOT NULL,
    `change_type` ENUM('created', 'updated', 'deleted', 'activated', 'deactivated') NOT NULL,
    
    -- Old and new values
    `old_price` DECIMAL(10,2),
    `new_price` DECIMAL(10,2),
    `price_change_amount` DECIMAL(10,2),
    `price_change_percentage` DECIMAL(5,2),
    
    -- Change details
    `change_reason` VARCHAR(255) COMMENT 'Reason for price change',
    `change_description` TEXT COMMENT 'Detailed description of changes',
    `old_conditions` JSON COMMENT 'Previous pricing conditions',
    `new_conditions` JSON COMMENT 'New pricing conditions',
    
    -- Change metadata
    `changed_by` INT NOT NULL COMMENT 'User who made the change',
    `change_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    
    -- Approval workflow
    `requires_approval` BOOLEAN DEFAULT FALSE,
    `approved_by` INT,
    `approved_at` DATETIME,
    `approval_notes` TEXT,
    
    -- Impact analysis
    `affected_customers` INT DEFAULT 0 COMMENT 'Number of affected customers',
    `revenue_impact` DECIMAL(15,2) DEFAULT 0 COMMENT 'Estimated revenue impact',
    `market_competitor_price` DECIMAL(10,2) COMMENT 'Competitor price for comparison',
    
    FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`pricing_id`) REFERENCES `service_pricing`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`changed_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_service_id` (`service_id`),
    INDEX `idx_pricing_id` (`pricing_id`),
    INDEX `idx_change_type` (`change_type`),
    INDEX `idx_change_date` (`change_date`),
    INDEX `idx_changed_by` (`changed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Pricing Rules Table
-- ============================================
CREATE TABLE IF NOT EXISTS `pricing_rules` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `rule_name` VARCHAR(255) NOT NULL,
    `rule_description` TEXT,
    `rule_type` ENUM('discount', 'surcharge', 'bulk_pricing', 'loyalty', 'promotional', 'seasonal') NOT NULL,
    
    -- Rule conditions
    `conditions` JSON NOT NULL COMMENT 'Rule conditions in JSON format',
    `actions` JSON NOT NULL COMMENT 'Rule actions in JSON format',
    
    -- Rule applicability
    `applicable_services` JSON COMMENT 'Service IDs this rule applies to',
    `applicable_customers` JSON COMMENT 'Customer types this rule applies to',
    `applicable_locations` JSON COMMENT 'Location IDs this rule applies to',
    
    -- Rule schedule
    `start_date` DATE,
    `end_date` DATE,
    `is_active` BOOLEAN DEFAULT TRUE,
    `priority` INT DEFAULT 0 COMMENT 'Rule priority (higher = more important)',
    
    -- Rule limits
    `usage_limit` INT DEFAULT 0 COMMENT 'Maximum times rule can be applied (0 = unlimited)',
    `usage_count` INT DEFAULT 0 COMMENT 'Current usage count',
    `customer_usage_limit` INT DEFAULT 0 COMMENT 'Per customer usage limit (0 = unlimited)',
    
    -- Rule metadata
    `created_by` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_rule_type` (`rule_type`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_priority` (`priority`),
    INDEX `idx_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Customer Pricing Table
-- ============================================
CREATE TABLE IF NOT EXISTS `customer_pricing` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    
    -- Custom pricing for customer
    `custom_price` DECIMAL(10,2) NOT NULL,
    `price_type` ENUM('fixed', 'percentage_discount', 'fixed_discount') DEFAULT 'fixed',
    `discount_percentage` DECIMAL(5,2) DEFAULT 0,
    `discount_amount` DECIMAL(10,2) DEFAULT 0,
    
    -- Validity
    `valid_from` DATE DEFAULT CURRENT_DATE,
    `valid_until` DATE DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    
    -- Agreement details
    `agreement_reference` VARCHAR(100) COMMENT 'Contract or agreement reference',
    `negotiated_by` INT COMMENT 'Who negotiated this price',
    `approved_by` INT COMMENT 'Who approved this pricing',
    `notes` TEXT,
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`negotiated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `unique_customer_service` (`customer_id`, `service_id`, `valid_from`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_service_id` (`service_id`),
    INDEX `idx_validity` (`valid_from`, `valid_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. Pricing Analytics Table
-- ============================================
CREATE TABLE IF NOT EXISTS `pricing_analytics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_id` INT NOT NULL,
    `pricing_id` INT NOT NULL,
    `date` DATE NOT NULL,
    
    -- Analytics data
    `times_used` INT DEFAULT 0 COMMENT 'Number of times this price was used',
    `total_revenue` DECIMAL(15,2) DEFAULT 0 COMMENT 'Total revenue from this price',
    `average_discount_given` DECIMAL(10,2) DEFAULT 0 COMMENT 'Average discount amount',
    `conversion_rate` DECIMAL(5,2) DEFAULT 0 COMMENT 'Conversion rate for this price',
    
    -- Customer analytics
    `new_customers` INT DEFAULT 0 COMMENT 'New customers using this price',
    `returning_customers` INT DEFAULT 0 COMMENT 'Returning customers using this price',
    `customer_satisfaction_score` DECIMAL(3,2) DEFAULT 0 COMMENT 'Average satisfaction score',
    
    -- Competitive analysis
    `competitor_price` DECIMAL(10,2) DEFAULT 0 COMMENT 'Average competitor price',
    `price_advantage` DECIMAL(5,2) DEFAULT 0 COMMENT 'Price advantage percentage',
    `market_share` DECIMAL(5,2) DEFAULT 0 COMMENT 'Market share percentage',
    
    -- Timestamps
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`pricing_id`) REFERENCES `service_pricing`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_service_pricing_date` (`service_id`, `pricing_id`, `date`),
    INDEX `idx_date` (`date`),
    INDEX `idx_times_used` (`times_used`),
    INDEX `idx_total_revenue` (`total_revenue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Default Services
-- ============================================
INSERT INTO `services` (`service_code`, `service_name_ar`, `service_name_en`, `service_description_ar`, `service_description_en`, `service_category`, `service_type`, `duration_minutes`, `inspection_points`, `icon_class`, `sort_order`) VALUES
('COMP_FULL', ' inspection shamel', 'Comprehensive Inspection', ' inspection shamel 200 nokta bel thakaf etefnai', 'Comprehensive inspection with 200 points using AI technology', 'inspection', 'standard', 90, 200, 'fas fa-car', 1),
('COMP_FAST', ' inspection sari', 'Quick Inspection', ' inspection sari 50 nokta', 'Quick inspection with 50 basic points', 'inspection', 'express', 30, 50, 'fas fa-tachometer-alt', 2),
('COMP_DYNO', 'fahs daino', 'Dyno Test', 'fahs quwa wa 'azm al-motor', 'Engine power and torque measurement test', 'test', 'standard', 60, 0, 'fas fa-tachometer-alt', 3),
('COMP_BRAKE', 'fahs framel', 'Brake Test', 'fahs nizam framel kamel', 'Complete brake system inspection and testing', 'test', 'standard', 45, 0, 'fas fa-stop-circle', 4),
('COMP_COMP', 'fahs kompyoter', 'Computer Test', 'fahs kompyoter sayara OBD2', 'Vehicle computer and OBD2 system diagnostic', 'test', 'standard', 30, 0, 'fas fa-laptop', 5),
('COMP_RIDE', 'fahs tatbiqat', 'Ride Hailing Inspection', 'fahs le tatbiqat uber, careem, didi', 'Special inspection for ride-hailing applications', 'inspection', 'standard', 60, 150, 'fas fa-taxi', 6),
('COMP_CERT', 'shahada mo'tamada', 'Certified Certificate', 'shahada mo'tamada ma'roofa', 'Official certified inspection certificate', 'certification', 'standard', 15, 0, 'fas fa-certificate', 7),
('COMP_PREMIUM', 'inspection premium', 'Premium Inspection', 'inspection premium bel khususiya', 'Premium inspection with priority service', 'inspection', 'premium', 120, 250, 'fas fa-crown', 8),
('COMP_MOBILE', 'inspection mobile', 'Mobile Inspection', 'inspection fi makanak', 'Mobile inspection service at your location', 'inspection', 'mobile', 90, 200, 'fas fa-mobile-alt', 9);

-- ============================================
-- Insert Default Pricing
-- ============================================
INSERT INTO `service_pricing` (`service_id`, `pricing_type`, `price_amount`, `pricing_unit`, `vehicle_type`, `created_by`) VALUES
(1, 'base_price', 1500.00, 'per_service', 'all', 1), -- Comprehensive Inspection
(2, 'base_price', 800.00, 'per_service', 'all', 1),  -- Quick Inspection
(3, 'base_price', 600.00, 'per_service', 'all', 1),  -- Dyno Test
(4, 'base_price', 400.00, 'per_service', 'all', 1),  -- Brake Test
(5, 'base_price', 300.00, 'per_service', 'all', 1),  -- Computer Test
(6, 'base_price', 1200.00, 'per_service', 'all', 1), -- Ride Hailing
(7, 'base_price', 200.00, 'per_service', 'all', 1),  -- Certificate
(8, 'base_price', 2500.00, 'per_service', 'all', 1), -- Premium
(9, 'base_price', 2000.00, 'per_service', 'all', 1); -- Mobile

-- Vehicle type pricing adjustments
INSERT INTO `service_pricing` (`service_id`, `pricing_type`, `price_amount`, `pricing_unit`, `vehicle_type`, `created_by`) VALUES
(1, 'vehicle_type', 2000.00, 'per_service', 'suv', 1),    -- SUV higher price
(1, 'vehicle_type', 2500.00, 'per_service', 'truck', 1),  -- Truck higher price
(1, 'vehicle_type', 800.00, 'per_service', 'motorcycle', 1), -- Motorcycle lower price
(2, 'vehicle_type', 1000.00, 'per_service', 'suv', 1),   -- SUV higher price
(2, 'vehicle_type', 1200.00, 'per_service', 'truck', 1), -- Truck higher price
(2, 'vehicle_type', 500.00, 'per_service', 'motorcycle', 1); -- Motorcycle lower price

-- ============================================
-- Insert Default Pricing Rules
-- ============================================
INSERT INTO `pricing_rules` (`rule_name`, `rule_description`, `rule_type`, `conditions`, `actions`, `created_by`) VALUES
('Loyalty Discount', 'Discount for repeat customers', 'discount', 
 '{"min_visits": 5, "customer_type": "individual"}',
 '{"discount_type": "percentage", "discount_value": 10}', 1),

('Corporate Discount', 'Special pricing for corporate clients', 'discount',
 '{"customer_type": "corporate", "min_services": 10}',
 '{"discount_type": "percentage", "discount_value": 15}', 1),

('Bulk Inspection Discount', 'Discount for multiple vehicle inspections', 'bulk_pricing',
 '{"service_count_min": 3}',
 '{"discount_type": "percentage", "discount_value": 20}', 1),

('Seasonal Promotion', 'Summer promotion discount', 'promotional',
 '{"date_range": {"start": "2024-06-01", "end": "2024-08-31"}}',
 '{"discount_type": "percentage", "discount_value": 25}', 1);

-- ============================================
-- Create Views for Common Queries
-- ============================================

-- Current Service Prices View
CREATE VIEW `current_service_prices` AS
SELECT 
    s.id as service_id,
    s.service_code,
    s.service_name_ar,
    s.service_name_en,
    s.service_category,
    s.service_type,
    s.is_active as service_active,
    sp.price_amount,
    sp.currency,
    sp.pricing_unit,
    sp.vehicle_type,
    sp.valid_from,
    sp.valid_until,
    sp.is_active as price_active,
    CASE 
        WHEN sp.valid_until IS NULL OR sp.valid_until >= CURRENT_DATE THEN 'current'
        ELSE 'expired'
    END as price_status
FROM services s
LEFT JOIN service_pricing sp ON s.id = sp.service_id
WHERE sp.pricing_type = 'base_price'
AND sp.is_active = TRUE
AND (sp.valid_until IS NULL OR sp.valid_until >= CURRENT_DATE)
ORDER BY s.sort_order;

-- Pricing Summary View
CREATE VIEW `pricing_summary` AS
SELECT 
    s.service_category,
    COUNT(*) as service_count,
    MIN(sp.price_amount) as min_price,
    MAX(sp.price_amount) as max_price,
    AVG(sp.price_amount) as avg_price,
    SUM(CASE WHEN s.is_active = 1 THEN 1 ELSE 0 END) as active_services
FROM services s
LEFT JOIN service_pricing sp ON s.id = sp.service_id
WHERE sp.pricing_type = 'base_price'
AND sp.is_active = TRUE
GROUP BY s.service_category;

-- ============================================
-- Create Triggers for Pricing History
-- ============================================

DELIMITER $$
CREATE TRIGGER `trigger_pricing_history_update`
AFTER UPDATE ON `service_pricing`
FOR EACH ROW
BEGIN
    -- Only log if price actually changed
    IF OLD.price_amount != NEW.price_amount OR OLD.is_active != NEW.is_active THEN
        INSERT INTO pricing_history 
        (service_id, pricing_id, change_type, old_price, new_price, 
         price_change_amount, price_change_percentage, change_reason,
         changed_by, old_conditions, new_conditions)
        VALUES 
        (NEW.service_id, NEW.id, 'updated', OLD.price_amount, NEW.price_amount,
         NEW.price_amount - OLD.price_amount,
         CASE WHEN OLD.price_amount > 0 THEN 
             ((NEW.price_amount - OLD.price_amount) / OLD.price_amount) * 100 
         ELSE 0 END,
         'Price updated by admin',
         NEW.updated_by,
         JSON_OBJECT('price_amount', OLD.price_amount, 'is_active', OLD.is_active),
         JSON_OBJECT('price_amount', NEW.price_amount, 'is_active', NEW.is_active));
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `trigger_pricing_history_insert`
AFTER INSERT ON `service_pricing`
FOR EACH ROW
BEGIN
    INSERT INTO pricing_history 
    (service_id, pricing_id, change_type, new_price, change_reason, changed_by, new_conditions)
    VALUES 
    (NEW.service_id, NEW.id, 'created', NEW.price_amount, 'New price created', 
     NEW.created_by, JSON_OBJECT('price_amount', NEW.price_amount, 'is_active', NEW.is_active));
END$$
DELIMITER ;

-- ============================================
-- Migration Complete
-- ============================================

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Pricing system migration completed', JSON_OBJECT(
    'migration_version', '004',
    'tables_created', 6,
    'views_created', 2,
    'triggers_created', 2,
    'default_services_inserted', 9,
    'default_pricing_inserted', 15,
    'default_rules_inserted', 4,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
