-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema with HR & Financial Management
-- Version: 2.0.0 - HR & Financial Management Edition
-- Created: October 2024

DROP DATABASE IF EXISTS aladin_b2b_ultimate;
CREATE DATABASE aladin_b2b_ultimate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE aladin_b2b_ultimate;

-- Sub-employees table (employees created by other employees)
CREATE TABLE sub_employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    parent_employee_id BIGINT UNSIGNED NOT NULL,
    team_id BIGINT UNSIGNED,
    employee_type ENUM('sub_employee', 'contractor', 'freelancer') DEFAULT 'sub_employee',
    position VARCHAR(100),
    hire_date DATE NOT NULL,
    compensation_type ENUM('commission_only', 'hourly', 'project_based') DEFAULT 'commission_only',
    base_commission_rate DECIMAL(5,2) DEFAULT 0,
    commission_balance DECIMAL(12,2) DEFAULT 0,
    territory_assignments JSON,
    skills JSON,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_sub_employees_parent (parent_employee_id),
    INDEX idx_sub_employees_team (team_id),
    INDEX idx_sub_employees_status (status)
);

-- Sub-employee teams table
CREATE TABLE sub_employee_teams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(100) NOT NULL,
    team_code VARCHAR(20) UNIQUE NOT NULL,
    parent_employee_id BIGINT UNSIGNED NOT NULL,
    parent_team_id BIGINT UNSIGNED NULL,
    team_type ENUM('sales_team', 'support_team', 'marketing_team', 'technical_team', 'business_dev_team', 'customer_service_team', 'exhibition_team', 'logistics_team') NOT NULL,
    team_description TEXT,
    commission_split_model ENUM('equal_split', 'performance_based', 'hierarchy_based', 'custom_split', 'team_leader_override') DEFAULT 'equal_split',
    territory_restrictions JSON,
    performance_targets JSON,
    team_budget DECIMAL(12,2) DEFAULT 0,
    max_team_size INT DEFAULT 10,
    current_team_size INT DEFAULT 0,
    hierarchy_level INT DEFAULT 1,
    status ENUM('active', 'inactive', 'suspended', 'under_review') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_team_id) REFERENCES sub_employee_teams(id) ON DELETE SET NULL,
    INDEX idx_teams_parent_employee (parent_employee_id),
    INDEX idx_teams_parent_team (parent_team_id),
    INDEX idx_teams_type (team_type),
    INDEX idx_teams_status (status)
);

-- Team memberships table
CREATE TABLE team_memberships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    role_in_team ENUM('team_leader', 'senior_member', 'regular_member', 'junior_member', 'trainee') DEFAULT 'regular_member',
    commission_percentage DECIMAL(5,2) DEFAULT 0,
    is_team_leader BOOLEAN DEFAULT FALSE,
    can_recruit BOOLEAN DEFAULT FALSE,
    can_manage_commissions BOOLEAN DEFAULT FALSE,
    performance_weight DECIMAL(3,2) DEFAULT 1.0,
    territory_access JSON,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    left_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE CASCADE,
    UNIQUE KEY unique_team_employee (team_id, employee_id),
    INDEX idx_team_memberships_team (team_id),
    INDEX idx_team_memberships_employee (employee_id)
);

-- =====================================================
-- TERRITORY MANAGEMENT TABLES
-- =====================================================

-- Territories table
CREATE TABLE territories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    territory_code VARCHAR(20) UNIQUE NOT NULL,
    territory_name VARCHAR(100) NOT NULL,
    territory_type ENUM('global', 'continent', 'country', 'region', 'state_province', 'city', 'postal_code', 'custom_area') NOT NULL,
    parent_territory_id BIGINT UNSIGNED NULL,
    country_code VARCHAR(3),
    region_code VARCHAR(10),
    state_province VARCHAR(100),
    city VARCHAR(100),
    postal_codes JSON,
    coordinates JSON,
    time_zone VARCHAR(50) DEFAULT 'UTC',
    currency VARCHAR(3) DEFAULT 'EUR',
    language VARCHAR(5) DEFAULT 'en',
    status ENUM('active', 'inactive', 'restricted', 'under_development') DEFAULT 'active',
    population BIGINT,
    economic_indicators JSON,
    business_regulations JSON,
    tax_information JSON,
    compliance_requirements JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    INDEX idx_territories_type (territory_type),
    INDEX idx_territories_parent (parent_territory_id),
    INDEX idx_territories_country (country_code),
    INDEX idx_territories_status (status)
);

-- Territory hierarchy table
CREATE TABLE territory_hierarchy (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    territory_id BIGINT UNSIGNED NOT NULL,
    parent_territory_id BIGINT UNSIGNED NULL,
    hierarchy_level INT NOT NULL,
    hierarchy_path TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    INDEX idx_territory_hierarchy_territory (territory_id),
    INDEX idx_territory_hierarchy_parent (parent_territory_id),
    INDEX idx_territory_hierarchy_level (hierarchy_level)
);

-- Employee territory assignments table
CREATE TABLE employee_territory_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    territory_id BIGINT UNSIGNED NOT NULL,
    access_level ENUM('read_only', 'read_write', 'full_control', 'admin') DEFAULT 'read_only',
    is_primary BOOLEAN DEFAULT FALSE,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    responsibilities JSON,
    restrictions JSON,
    assigned_by BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_employee_territories_employee (employee_id),
    INDEX idx_employee_territories_territory (territory_id),
    INDEX idx_employee_territories_primary (is_primary)
);

-- =====================================================
-- PAYROLL AND COMMISSION TABLES
-- =====================================================

-- Payroll periods table
CREATE TABLE payroll_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    period_name VARCHAR(50) NOT NULL,
    period_type ENUM('weekly', 'bi_weekly', 'monthly', 'quarterly', 'annual') DEFAULT 'monthly',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    pay_date DATE NOT NULL,
    status ENUM('draft', 'processing', 'completed', 'cancelled') DEFAULT 'draft',
    total_gross_pay DECIMAL(15,2) DEFAULT 0,
    total_deductions DECIMAL(15,2) DEFAULT 0,
    total_net_pay DECIMAL(15,2) DEFAULT 0,
    employee_count INT DEFAULT 0,
    processed_by BIGINT UNSIGNED,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (processed_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_payroll_periods_dates (start_date, end_date),
    INDEX idx_payroll_periods_status (status)
);

-- Employee payroll records table
CREATE TABLE employee_payroll_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    base_salary DECIMAL(10,2) DEFAULT 0,
    overtime_hours DECIMAL(5,2) DEFAULT 0,
    overtime_rate DECIMAL(8,2) DEFAULT 0,
    overtime_pay DECIMAL(10,2) DEFAULT 0,
    commission_amount DECIMAL(10,2) DEFAULT 0,
    bonus_amount DECIMAL(10,2) DEFAULT 0,
    allowances DECIMAL(10,2) DEFAULT 0,
    gross_pay DECIMAL(12,2) DEFAULT 0,
    tax_deductions DECIMAL(10,2) DEFAULT 0,
    social_security_deductions DECIMAL(10,2) DEFAULT 0,
    insurance_deductions DECIMAL(10,2) DEFAULT 0,
    other_deductions DECIMAL(10,2) DEFAULT 0,
    total_deductions DECIMAL(10,2) DEFAULT 0,
    net_pay DECIMAL(12,2) DEFAULT 0,
    payment_method ENUM('bank_transfer', 'check', 'cash', 'digital_wallet') DEFAULT 'bank_transfer',
    payment_status ENUM('pending', 'processing', 'paid', 'failed') DEFAULT 'pending',
    payment_date DATE NULL,
    payment_reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_payroll_employee (payroll_period_id, employee_id),
    INDEX idx_payroll_records_period (payroll_period_id),
    INDEX idx_payroll_records_employee (employee_id),
    INDEX idx_payroll_records_status (payment_status)
);

-- Commission structures table
CREATE TABLE commission_structures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    structure_name VARCHAR(100) NOT NULL,
    structure_type ENUM('flat_rate', 'tiered', 'performance_based', 'team_based', 'territory_based') NOT NULL,
    employee_type ENUM('SALES', 'MARKETING', 'BUSINESS_DEVELOPMENT', 'CUSTOMER_SERVICE', 'TECHNICAL', 'MANAGEMENT', 'EXHIBITION', 'LOGISTICS', 'FINANCE', 'HR') NULL,
    territory_id BIGINT UNSIGNED NULL,
    team_id BIGINT UNSIGNED NULL,
    base_rate DECIMAL(5,2) DEFAULT 0,
    tier_structure JSON,
    performance_thresholds JSON,
    bonus_structure JSON,
    effective_date DATE NOT NULL,
    expiry_date DATE NULL,
    status ENUM('active', 'inactive', 'draft') DEFAULT 'active',
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_commission_structures_type (structure_type),
    INDEX idx_commission_structures_employee_type (employee_type),
    INDEX idx_commission_structures_status (status)
);

-- Commission transactions table
CREATE TABLE commission_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_reference VARCHAR(100) UNIQUE NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('sale', 'exhibition_booking', 'membership_signup', 'referral', 'bonus', 'adjustment') NOT NULL,
    source_transaction_id BIGINT UNSIGNED,
    customer_id BIGINT UNSIGNED,
    transaction_amount DECIMAL(12,2) NOT NULL,
    commission_rate DECIMAL(5,2) NOT NULL,
    commission_amount DECIMAL(10,2) NOT NULL,
    commission_structure_id BIGINT UNSIGNED,
    team_id BIGINT UNSIGNED NULL,
    territory_id BIGINT UNSIGNED NULL,
    transaction_date DATE NOT NULL,
    payment_status ENUM('pending', 'approved', 'paid', 'cancelled') DEFAULT 'pending',
    payment_date DATE NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (commission_structure_id) REFERENCES commission_structures(id) ON DELETE SET NULL,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE SET NULL,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    INDEX idx_commission_transactions_employee (employee_id),
    INDEX idx_commission_transactions_type (transaction_type),
    INDEX idx_commission_transactions_date (transaction_date),
    INDEX idx_commission_transactions_status (payment_status)
);

-- Team commission distributions table
CREATE TABLE team_commission_distributions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id BIGINT UNSIGNED NOT NULL,
    transaction_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    commission_amount DECIMAL(10,2) NOT NULL,
    commission_percentage DECIMAL(5,2) NOT NULL,
    calculation_method ENUM('equal_split', 'performance_based', 'hierarchy_based', 'custom_split') NOT NULL,
    performance_factor DECIMAL(3,2) DEFAULT 1.0,
    payout_id BIGINT UNSIGNED NULL,
    payout_status ENUM('pending', 'processed', 'cancelled') DEFAULT 'pending',
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES commission_transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_team_commissions_team (team_id),
    INDEX idx_team_commissions_transaction (transaction_id),
    INDEX idx_team_commissions_employee (employee_id),
    INDEX idx_team_commissions_status (payout_status)
);

-- Team payouts table
CREATE TABLE team_payouts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    payout_period_start DATE NOT NULL,
    payout_period_end DATE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    transaction_count INT NOT NULL,
    payout_method ENUM('bank_transfer', 'check', 'digital_wallet', 'internal_transfer') DEFAULT 'bank_transfer',
    payout_status ENUM('pending', 'processing', 'processed', 'failed', 'cancelled') DEFAULT 'pending',
    processed_at TIMESTAMP NULL,
    payment_reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_team_payouts_team (team_id),
    INDEX idx_team_payouts_employee (employee_id),
    INDEX idx_team_payouts_period (payout_period_start, payout_period_end),
    INDEX idx_team_payouts_status (payout_status)
);

-- =====================================================
-- FINANCIAL CONTROLLING TABLES
-- =====================================================

-- Cost centers table
CREATE TABLE cost_centers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_center_code VARCHAR(20) UNIQUE NOT NULL,
    cost_center_name VARCHAR(100) NOT NULL,
    cost_center_type ENUM('department', 'project', 'territory', 'team', 'exhibition') NOT NULL,
    parent_cost_center_id BIGINT UNSIGNED NULL,
    responsible_employee_id BIGINT UNSIGNED,
    territory_id BIGINT UNSIGNED NULL,
    budget_annual DECIMAL(15,2) DEFAULT 0,
    budget_monthly DECIMAL(12,2) DEFAULT 0,
    actual_costs_ytd DECIMAL(15,2) DEFAULT 0,
    actual_costs_mtd DECIMAL(12,2) DEFAULT 0,
    status ENUM('active', 'inactive', 'closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_cost_center_id) REFERENCES cost_centers(id) ON DELETE SET NULL,
    FOREIGN KEY (responsible_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    INDEX idx_cost_centers_type (cost_center_type),
    INDEX idx_cost_centers_responsible (responsible_employee_id),
    INDEX idx_cost_centers_territory (territory_id)
);

-- Revenue transactions table
CREATE TABLE revenue_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_reference VARCHAR(100) UNIQUE NOT NULL,
    transaction_type ENUM('product_sale', 'membership_fee', 'exhibition_booking', 'commission_fee', 'service_fee', 'subscription') NOT NULL,
    customer_id BIGINT UNSIGNED,
    employee_id BIGINT UNSIGNED,
    territory_id BIGINT UNSIGNED,
    cost_center_id BIGINT UNSIGNED,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    tax_amount DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(50),
    transaction_date DATE NOT NULL,
    status ENUM('pending', 'completed', 'cancelled', 'refunded') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id) ON DELETE SET NULL,
    INDEX idx_revenue_transactions_type (transaction_type),
    INDEX idx_revenue_transactions_employee (employee_id),
    INDEX idx_revenue_transactions_territory (territory_id),
    INDEX idx_revenue_transactions_date (transaction_date),
    INDEX idx_revenue_transactions_status (status)
);

-- Expense transactions table
CREATE TABLE expense_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_reference VARCHAR(100) UNIQUE NOT NULL,
    expense_type ENUM('salary', 'commission', 'office_rent', 'utilities', 'marketing', 'travel', 'equipment', 'software', 'training', 'other') NOT NULL,
    cost_center_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NULL,
    territory_id BIGINT UNSIGNED NULL,
    vendor_name VARCHAR(100),
    description TEXT,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    tax_amount DECIMAL(10,2) DEFAULT 0,
    expense_date DATE NOT NULL,
    payment_method VARCHAR(50),
    payment_status ENUM('pending', 'approved', 'paid', 'rejected') DEFAULT 'pending',
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    receipt_url VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_expense_transactions_type (expense_type),
    INDEX idx_expense_transactions_cost_center (cost_center_id),
    INDEX idx_expense_transactions_employee (employee_id),
    INDEX idx_expense_transactions_date (expense_date),
    INDEX idx_expense_transactions_status (payment_status)
);

-- Financial reports table
CREATE TABLE financial_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_name VARCHAR(100) NOT NULL,
    report_type ENUM('profit_loss', 'cost_analysis', 'revenue_analysis', 'employee_performance', 'territory_performance', 'commission_analysis') NOT NULL,
    report_period_start DATE NOT NULL,
    report_period_end DATE NOT NULL,
    territory_id BIGINT UNSIGNED NULL,
    cost_center_id BIGINT UNSIGNED NULL,
    report_data JSON,
    total_revenue DECIMAL(15,2) DEFAULT 0,
    total_expenses DECIMAL(15,2) DEFAULT 0,
    net_profit DECIMAL(15,2) DEFAULT 0,
    generated_by BIGINT UNSIGNED,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('draft', 'final', 'archived') DEFAULT 'draft',
    file_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
    FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id) ON DELETE SET NULL,
    FOREIGN KEY (generated_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_financial_reports_type (report_type),
    INDEX idx_financial_reports_period (report_period_start, report_period_end),
    INDEX idx_financial_reports_territory (territory_id)
);

-- =====================================================
-- EXHIBITION MANAGEMENT TABLES (Enhanced)
-- =====================================================

-- Exhibitions table
CREATE TABLE exhibitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exhibition_code VARCHAR(20) UNIQUE NOT NULL,
    exhibition_name VARCHAR(200) NOT NULL,
    exhibition_type ENUM('permanent_365', 'temporary', 'virtual', 'hybrid') DEFAULT 'permanent_365',
    territory_id BIGINT UNSIGNED NOT NULL,
    venue_name VARCHAR(200) NOT NULL,
    venue_address TEXT NOT NULL,
    venue_coordinates JSON,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    total_area_sqm DECIMAL(10,2) NOT NULL,
    available_area_sqm DECIMAL(10,2) NOT NULL,
    booth_count_total INT NOT NULL,
    booth_count_occupied INT DEFAULT 0,
    booth_count_reserved INT DEFAULT 0,
    pricing_structure JSON,
    facilities JSON,
    operating_hours JSON,
    contact_person_id BIGINT UNSIGNED,
    status ENUM('planning', 'active', 'suspended', 'closed') DEFAULT 'planning',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_person_id) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_exhibitions_territory (territory_id),
    INDEX idx_exhibitions_type (exhibition_type),
    INDEX idx_exhibitions_status (status),
    INDEX idx_exhibitions_dates (start_date, end_date)
);

-- Exhibition booths table
CREATE TABLE exhibition_booths (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exhibition_id BIGINT UNSIGNED NOT NULL,
    booth_number VARCHAR(20) NOT NULL,
    booth_size_category ENUM('small_3x3', 'medium_6x6', 'large_9x9', 'premium_12x12', 'custom') NOT NULL,
    area_sqm DECIMAL(8,2) NOT NULL,
    position_coordinates JSON,
    monthly_price DECIMAL(10,2) NOT NULL,
    features JSON,
    utilities_included JSON,
    status ENUM('available', 'reserved', 'occupied', 'maintenance') DEFAULT 'available',
    current_tenant_id BIGINT UNSIGNED NULL,
    reservation_expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exhibition_id) REFERENCES exhibitions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_exhibition_booth (exhibition_id, booth_number),
    INDEX idx_exhibition_booths_exhibition (exhibition_id),
    INDEX idx_exhibition_booths_size (booth_size_category),
    INDEX idx_exhibition_booths_status (status)
);

-- Exhibition bookings table
CREATE TABLE exhibition_bookings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_reference VARCHAR(100) UNIQUE NOT NULL,
    exhibition_id BIGINT UNSIGNED NOT NULL,
    booth_id BIGINT UNSIGNED NOT NULL,
    seller_id BIGINT UNSIGNED NOT NULL,
    coordinator_employee_id BIGINT UNSIGNED,
    booking_type ENUM('monthly', 'quarterly', 'annual', 'custom') DEFAULT 'monthly',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    booth_count INT DEFAULT 1,
    total_area_sqm DECIMAL(8,2) NOT NULL,
    monthly_rate DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    payment_terms ENUM('monthly', 'quarterly', 'annual', 'upfront') DEFAULT 'monthly',
    payment_status ENUM('pending', 'partial', 'paid', 'overdue', 'cancelled') DEFAULT 'pending',
    booking_status ENUM('pending', 'confirmed', 'active', 'expired', 'cancelled') DEFAULT 'pending',
    special_requirements TEXT,
    setup_date DATE,
    teardown_date DATE,
    revenue DECIMAL(12,2) DEFAULT 0,
    customer_satisfaction DECIMAL(3,1) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exhibition_id) REFERENCES exhibitions(id) ON DELETE CASCADE,
    FOREIGN KEY (booth_id) REFERENCES exhibition_booths(id) ON DELETE CASCADE,
    FOREIGN KEY (coordinator_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_exhibition_bookings_exhibition (exhibition_id),
    INDEX idx_exhibition_bookings_booth (booth_id),
    INDEX idx_exhibition_bookings_seller (seller_id),
    INDEX idx_exhibition_bookings_dates (start_date, end_date),
    INDEX idx_exhibition_bookings_status (booking_status)
);

-- =====================================================
-- COMPLIANCE AND AUDIT TABLES
-- =====================================================

-- Territory compliance table
CREATE TABLE territory_compliance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    territory_id BIGINT UNSIGNED NOT NULL,
    compliance_type ENUM('gdpr', 'labor_law', 'tax_law', 'business_license', 'safety_standards', 'data_protection') NOT NULL,
    requirement_description TEXT NOT NULL,
    compliance_status ENUM('compliant', 'non_compliant', 'pending', 'under_review') DEFAULT 'pending',
    due_date DATE,
    completion_date DATE NULL,
    responsible_employee_id BIGINT UNSIGNED,
    evidence_documents JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    FOREIGN KEY (responsible_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_territory_compliance_territory (territory_id),
    INDEX idx_territory_compliance_type (compliance_type),
    INDEX idx_territory_compliance_status (compliance_status),
    INDEX idx_territory_compliance_due_date (due_date)
);

-- Activity logs table
CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    employee_id BIGINT UNSIGNED,
    activity_type ENUM('login', 'logout', 'create', 'update', 'delete', 'view', 'export', 'import', 'payment', 'commission') NOT NULL,
    entity_type VARCHAR(50),
    entity_id BIGINT UNSIGNED,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    additional_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_activity_logs_user (user_id),
    INDEX idx_activity_logs_employee (employee_id),
    INDEX idx_activity_logs_type (activity_type),
    INDEX idx_activity_logs_entity (entity_type, entity_id),
    INDEX idx_activity_logs_created (created_at)
);

-- Territory activity log table
CREATE TABLE territory_activity_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    territory_id BIGINT UNSIGNED NOT NULL,
    activity_type ENUM('employee_assigned', 'employee_removed', 'compliance_updated', 'performance_review', 'budget_updated') NOT NULL,
    activity_data JSON,
    performed_by BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_territory_activity_territory (territory_id),
    INDEX idx_territory_activity_type (activity_type),
    INDEX idx_territory_activity_created (created_at)
);

-- =====================================================
-- PERFORMANCE TRACKING TABLES
-- =====================================================

-- Employee performance metrics table
CREATE TABLE employee_performance_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    evaluation_period_start DATE NOT NULL,
    evaluation_period_end DATE NOT NULL,
    performance_score DECIMAL(5,2) DEFAULT 0,
    revenue_generated DECIMAL(12,2) DEFAULT 0,
    commission_earned DECIMAL(10,2) DEFAULT 0,
    customers_acquired INT DEFAULT 0,
    customer_satisfaction_avg DECIMAL(3,2) DEFAULT 0,
    goals_achieved INT DEFAULT 0,
    goals_total INT DEFAULT 0,
    training_hours DECIMAL(5,2) DEFAULT 0,
    attendance_percentage DECIMAL(5,2) DEFAULT 100,
    peer_rating DECIMAL(3,2) DEFAULT 0,
    manager_rating DECIMAL(3,2) DEFAULT 0,
    self_rating DECIMAL(3,2) DEFAULT 0,
    improvement_areas TEXT,
    achievements TEXT,
    next_period_goals TEXT,
    evaluated_by BIGINT UNSIGNED,
    evaluation_date DATE,
    status ENUM('draft', 'completed', 'approved') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (evaluated_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_performance_metrics_employee (employee_id),
    INDEX idx_performance_metrics_period (evaluation_period_start, evaluation_period_end),
    INDEX idx_performance_metrics_score (performance_score)
);

-- Team performance metrics table
CREATE TABLE team_performance_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id BIGINT UNSIGNED NOT NULL,
    evaluation_period_start DATE NOT NULL,
    evaluation_period_end DATE NOT NULL,
    team_performance_score DECIMAL(5,2) DEFAULT 0,
    total_revenue DECIMAL(15,2) DEFAULT 0,
    total_commission DECIMAL(12,2) DEFAULT 0,
    member_count INT DEFAULT 0,
    avg_member_performance DECIMAL(5,2) DEFAULT 0,
    customer_acquisition_count INT DEFAULT 0,
    customer_satisfaction_avg DECIMAL(3,2) DEFAULT 0,
    team_goals_achieved INT DEFAULT 0,
    team_goals_total INT DEFAULT 0,
    collaboration_score DECIMAL(3,2) DEFAULT 0,
    innovation_score DECIMAL(3,2) DEFAULT 0,
    efficiency_score DECIMAL(3,2) DEFAULT 0,
    strengths TEXT,
    improvement_areas TEXT,
    next_period_objectives TEXT,
    evaluated_by BIGINT UNSIGNED,
    evaluation_date DATE,
    status ENUM('draft', 'completed', 'approved') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES sub_employee_teams(id) ON DELETE CASCADE,
    FOREIGN KEY (evaluated_by) REFERENCES employees(id) ON DELETE SET NULL,
    INDEX idx_team_performance_team (team_id),
    INDEX idx_team_performance_period (evaluation_period_start, evaluation_period_end),
    INDEX idx_team_performance_score (team_performance_score)
);

-- =====================================================
-- SYSTEM CONFIGURATION TABLES
-- =====================================================

-- System settings table
CREATE TABLE system_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('string', 'integer', 'decimal', 'boolean', 'json') DEFAULT 'string',
    category VARCHAR(50) DEFAULT 'general',
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_system_settings_category (category),
    INDEX idx_system_settings_public (is_public)
);

-- Insert global territory
INSERT INTO territories (territory_code, territory_name, territory_type, country_code, status) VALUES
('GLB-GLO-0001', 'Global', 'global', 'GLB', 'active');

-- Insert default cost center
INSERT INTO cost_centers (cost_center_code, cost_center_name, cost_center_type, responsible_employee_id, budget_annual) VALUES
('CC-ADM-001', 'Administration', 'department', 1, 1000000.00);

-- Insert default commission structure
INSERT INTO commission_structures (structure_name, structure_type, base_rate, effective_date, created_by) VALUES
('Default Sales Commission', 'flat_rate', 5.00, CURDATE(), 1),
('Default Marketing Commission', 'flat_rate', 3.00, CURDATE(), 1),
('Default Exhibition Commission', 'flat_rate', 7.50, CURDATE(), 1);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, category, description) VALUES
('company_name', 'ALADIN B2B Plus Ultimate Platform', 'string', 'general', 'Company name'),
('default_currency', 'EUR', 'string', 'financial', 'Default currency'),
('default_language', 'en', 'string', 'localization', 'Default language'),
('payroll_frequency', 'monthly', 'string', 'hr', 'Default payroll frequency'),
('commission_payment_day', '15', 'integer', 'financial', 'Day of month for commission payments'),
('max_team_size', '20', 'integer', 'hr', 'Maximum team size'),
('performance_review_frequency', 'quarterly', 'string', 'hr', 'Performance review frequency');


-- =====================================================
-- CREATE INDEXES FOR PERFORMANCE
-- =====================================================

-- Additional performance indexes
CREATE INDEX idx_revenue_transactions_period ON revenue_transactions (DATE_FORMAT(transaction_date, '%Y-%m'));
CREATE INDEX idx_commission_transactions_period ON commission_transactions (DATE_FORMAT(transaction_date, '%Y-%m'));
CREATE INDEX idx_expense_transactions_period ON expense_transactions (DATE_FORMAT(expense_date, '%Y-%m'));
CREATE INDEX idx_teams_performance ON sub_employee_teams (status, current_team_size);

-- =====================================================
-- STORED PROCEDURES FOR COMMON OPERATIONS
-- =====================================================

DELIMITER //

-- Procedure to calculate employee commission
CREATE PROCEDURE CalculateEmployeeCommission(
    IN p_employee_id BIGINT,
    IN p_transaction_amount DECIMAL(12,2),
    IN p_transaction_type VARCHAR(50),
    OUT p_commission_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_commission_rate DECIMAL(5,2) DEFAULT 0;
    
    -- Calculate commission
    SET p_commission_amount = p_transaction_amount * (v_commission_rate / 100);
END //

-- Procedure to process team commission distribution
CREATE PROCEDURE ProcessTeamCommissionDistribution(
    IN p_team_id BIGINT,
    IN p_total_commission DECIMAL(10,2),
    IN p_transaction_id BIGINT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_employee_id BIGINT;
    DECLARE v_commission_percentage DECIMAL(5,2);
    DECLARE v_member_commission DECIMAL(10,2);
    
    DECLARE member_cursor CURSOR FOR
        SELECT employee_id, commission_percentage
        FROM team_memberships
        WHERE team_id = p_team_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN member_cursor;
    
    member_loop: LOOP
        FETCH member_cursor INTO v_employee_id, v_commission_percentage;
        IF done THEN
            LEAVE member_loop;
        END IF;
        
        SET v_member_commission = p_total_commission * (v_commission_percentage / 100);
        
        INSERT INTO team_commission_distributions (
            team_id, transaction_id, employee_id, commission_amount, 
            commission_percentage, calculation_method
        ) VALUES (
            p_team_id, p_transaction_id, v_employee_id, v_member_commission,
            v_commission_percentage, 'custom_split'
        );
        
    END LOOP;
    
    CLOSE member_cursor;
END //

DELIMITER ;

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

DELIMITER //

-- Trigger to update team member count
CREATE TRIGGER update_team_member_count_insert
AFTER INSERT ON team_memberships
FOR EACH ROW
BEGIN
    UPDATE sub_employee_teams 
    SET current_team_size = (
        SELECT COUNT(*) 
        FROM team_memberships 
        WHERE team_id = NEW.team_id AND left_at IS NULL
    )
    WHERE id = NEW.team_id;
END //

CREATE TRIGGER update_team_member_count_delete
AFTER DELETE ON team_memberships
FOR EACH ROW
BEGIN
    UPDATE sub_employee_teams 
    SET current_team_size = (
        SELECT COUNT(*) 
        FROM team_memberships 
        WHERE team_id = OLD.team_id AND left_at IS NULL
    )
    WHERE id = OLD.team_id;
END //

DELIMITER ;

-- =====================================================
-- FINAL SETUP
-- =====================================================

-- Grant permissions (adjust as needed for your environment)
-- GRANT ALL PRIVILEGES ON aladin_b2b_ultimate.* TO 'aladin_user'@'localhost';
-- FLUSH PRIVILEGES;

-- Set database character set and collation
ALTER DATABASE aladin_b2b_ultimate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- DATABASE SCHEMA COMPLETE
-- =====================================================

SELECT 'ALADIN B2B Plus Ultimate Platform Database Schema with HR & Financial Management - Installation Complete!' as Status;
