-- KYCAML.AI Onboarding Sessions Table
-- Progressive onboarding data collection based on onboarding_framework.md

CREATE TABLE IF NOT EXISTS `onboarding_sessions` (
  -- Primary identifiers
  `onboarding_sessions_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `onboarding_sessions_hash` VARCHAR(255) NOT NULL UNIQUE,
  `onboarding_status` VARCHAR(50) DEFAULT 'in_progress',
  `current_step` INT(11) DEFAULT 1,
  `datetime_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `datetime_updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `datetime_completed` DATETIME DEFAULT NULL,
  
  -- Phase 1: Entity Identification & Legal Foundation
  -- 1.1 Company Legal Information
  `company_name` VARCHAR(500) DEFAULT NULL,
  `business_registration_number` VARCHAR(255) DEFAULT NULL,
  `year_founded` INT(4) DEFAULT NULL,
  `date_of_incorporation` DATE DEFAULT NULL,
  `entity_type` VARCHAR(100) DEFAULT NULL,
  `jurisdiction_of_incorporation` VARCHAR(255) DEFAULT NULL,
  `physical_business_address` TEXT DEFAULT NULL,
  `headquarters_location` TEXT DEFAULT NULL,
  
  -- 1.2 Corporate Structure & Ownership
  `parent_company_name` VARCHAR(500) DEFAULT NULL,
  `group_structure` TEXT DEFAULT NULL,
  `ubo_information` JSON DEFAULT NULL,
  
  -- 1.3 Governance & Management
  `directors_list` JSON DEFAULT NULL,
  `shareholders_list` JSON DEFAULT NULL,
  
  -- Phase 2: Regulatory & Licensing Compliance
  -- 2.1 Licensing Status
  `license_status` VARCHAR(50) DEFAULT NULL,
  `license_types` JSON DEFAULT NULL,
  `license_numbers` TEXT DEFAULT NULL,
  `licensing_jurisdictions` JSON DEFAULT NULL,
  `regulatory_authority` VARCHAR(500) DEFAULT NULL,
  `license_expiry_dates` JSON DEFAULT NULL,
  
  -- 2.2 Operational Jurisdictions
  `operational_jurisdictions` JSON DEFAULT NULL,
  `online_gambling_licensing` TEXT DEFAULT NULL,
  `geographic_restrictions` JSON DEFAULT NULL,
  `local_compliance_regulations` JSON DEFAULT NULL,
  
  -- 2.3 Regulatory Identifiers
  `swift_bic_code` VARCHAR(50) DEFAULT NULL,
  `lei_code` VARCHAR(50) DEFAULT NULL,
  `tax_identification_numbers` JSON DEFAULT NULL,
  
  -- Phase 3: KYC Documentation Collection
  -- 3.1 Corporate Documents
  `certificate_of_incorporation_path` VARCHAR(500) DEFAULT NULL,
  `articles_of_association_path` VARCHAR(500) DEFAULT NULL,
  `certificate_good_standing_path` VARCHAR(500) DEFAULT NULL,
  `proof_of_business_address_path` VARCHAR(500) DEFAULT NULL,
  `group_structure_chart_path` VARCHAR(500) DEFAULT NULL,
  `register_directors_shareholders_path` VARCHAR(500) DEFAULT NULL,
  
  -- 3.2 Individual Identity Documents
  `directors_documents` JSON DEFAULT NULL,
  `ubos_documents` JSON DEFAULT NULL,
  
  -- 3.3 Financial & Banking Documents
  `bank_name` VARCHAR(500) DEFAULT NULL,
  `bank_address` TEXT DEFAULT NULL,
  `bank_account_number` VARCHAR(255) DEFAULT NULL,
  `bank_iban` VARCHAR(100) DEFAULT NULL,
  `bank_swift_bic` VARCHAR(50) DEFAULT NULL,
  `bank_account_holder_name` VARCHAR(500) DEFAULT NULL,
  `bank_reference_letter_path` VARCHAR(500) DEFAULT NULL,
  `financial_statements_path` VARCHAR(500) DEFAULT NULL,
  `tax_returns_path` VARCHAR(500) DEFAULT NULL,
  
  -- Phase 4: Business Operations & Industry Profile
  -- 4.1 Business Description
  `industry_verticals` JSON DEFAULT NULL,
  `website_urls` JSON DEFAULT NULL,
  `domain_age` VARCHAR(100) DEFAULT NULL,
  `business_model_description` TEXT DEFAULT NULL,
  `target_customer_profile` VARCHAR(255) DEFAULT NULL,
  
  -- 4.2 Online Presence & Marketing
  `fan_page_urls` JSON DEFAULT NULL,
  `landing_page_urls` JSON DEFAULT NULL,
  `ad_creative_samples_path` VARCHAR(500) DEFAULT NULL,
  `traffic_sources_breakdown` JSON DEFAULT NULL,
  `marketing_channels` JSON DEFAULT NULL,
  
  -- 4.3 Customer & Geographic Distribution
  `customer_geography_breakdown` JSON DEFAULT NULL,
  `target_geos` JSON DEFAULT NULL,
  `regional_split` JSON DEFAULT NULL,
  `blacklisted_networks_geos` TEXT DEFAULT NULL,
  
  -- Phase 5: Transaction & Volume Profile
  -- 5.1 Processing Volume
  `monthly_processing_volume` DECIMAL(15,2) DEFAULT NULL,
  `monthly_processing_volume_currency` VARCHAR(10) DEFAULT 'USD',
  `average_ticket_size` DECIMAL(15,2) DEFAULT NULL,
  `transaction_count_monthly` INT(11) DEFAULT NULL,
  `historical_volume_trends` JSON DEFAULT NULL,
  `expected_monthly_volume` DECIMAL(15,2) DEFAULT NULL,
  `seasonal_variations` TEXT DEFAULT NULL,
  
  -- 5.2 Currency & Settlement
  `main_processing_currencies` JSON DEFAULT NULL,
  `settlement_preferences` JSON DEFAULT NULL,
  `settlement_currencies` JSON DEFAULT NULL,
  `settlement_frequency` VARCHAR(50) DEFAULT NULL,
  `payout_frequency_method` VARCHAR(255) DEFAULT NULL,
  
  -- 5.3 Transaction Characteristics
  `recurring_vs_onetime_split` VARCHAR(50) DEFAULT NULL,
  `subscription_model` TEXT DEFAULT NULL,
  `average_customer_lifetime_value` DECIMAL(15,2) DEFAULT NULL,
  `lead_vs_sale_model` VARCHAR(255) DEFAULT NULL,
  
  -- Phase 6: Payment Infrastructure Requirements
  -- 6.1 Payment Methods & Rails
  `payment_rails_required` JSON DEFAULT NULL,
  `supported_payment_methods` JSON DEFAULT NULL,
  `3d_secure_usage` VARCHAR(50) DEFAULT NULL,
  `3d_secure_percentage` DECIMAL(5,2) DEFAULT NULL,
  
  -- 6.2 Technical Integration
  `tech_integration_preference` VARCHAR(100) DEFAULT NULL,
  `current_payment_gateway` VARCHAR(255) DEFAULT NULL,
  `technical_stack` TEXT DEFAULT NULL,
  `api_documentation_requirements` TEXT DEFAULT NULL,
  `webhook_callback_requirements` TEXT DEFAULT NULL,
  
  -- 6.3 Pricing & Fee Structure
  `existing_fees_fixed` DECIMAL(10,2) DEFAULT NULL,
  `existing_fees_percentage` DECIMAL(5,2) DEFAULT NULL,
  `existing_fees_monthly` DECIMAL(10,2) DEFAULT NULL,
  `preferred_pricing_model` VARCHAR(100) DEFAULT NULL,
  `expected_pricing` TEXT DEFAULT NULL,
  `minimum_volume_commitments` DECIMAL(15,2) DEFAULT NULL,
  
  -- Phase 7: Risk Assessment & Processing History
  -- 7.1 Processing History
  `previous_acquirers` JSON DEFAULT NULL,
  `years_in_business` INT(11) DEFAULT NULL,
  `industry_experience_years` INT(11) DEFAULT NULL,
  `prior_issues` JSON DEFAULT NULL,
  
  -- 7.2 Risk Metrics
  `chargeback_rate` DECIMAL(5,2) DEFAULT NULL,
  `refund_rate` DECIMAL(5,2) DEFAULT NULL,
  `fraud_rate` DECIMAL(5,2) DEFAULT NULL,
  `dispute_resolution_process` TEXT DEFAULT NULL,
  `refund_policy` TEXT DEFAULT NULL,
  `refund_policy_url` VARCHAR(500) DEFAULT NULL,
  
  -- 7.3 Fraud & Chargeback Management
  `chargeback_management_tools` JSON DEFAULT NULL,
  `fraud_prevention_measures` JSON DEFAULT NULL,
  `transaction_monitoring_tools` TEXT DEFAULT NULL,
  
  -- Phase 8: AML/Compliance & Risk Controls
  -- 8.1 AML/KYC Procedures
  `aml_kyc_process_description` TEXT DEFAULT NULL,
  `cdd_process` TEXT DEFAULT NULL,
  `edd_triggers` TEXT DEFAULT NULL,
  `sanctions_screening_provider` VARCHAR(255) DEFAULT NULL,
  `sanctions_screening_frequency` VARCHAR(100) DEFAULT NULL,
  `pep_checks` VARCHAR(50) DEFAULT NULL,
  `adverse_media_screening` VARCHAR(50) DEFAULT NULL,
  
  -- 8.2 Compliance Standards
  `compliance_standards` JSON DEFAULT NULL,
  `pci_dss_level` VARCHAR(10) DEFAULT NULL,
  `compliance_certifications_path` VARCHAR(500) DEFAULT NULL,
  `data_protection_measures` TEXT DEFAULT NULL,
  `privacy_policy_url` VARCHAR(500) DEFAULT NULL,
  
  -- 8.3 Risk Appetite & Restrictions
  `risk_appetite` VARCHAR(50) DEFAULT NULL,
  `onboarding_restrictions` TEXT DEFAULT NULL,
  `prohibited_transaction_types` JSON DEFAULT NULL,
  `restricted_countries_regions` JSON DEFAULT NULL,
  
  -- Phase 9: Business Priorities & Service Requirements
  `service_priorities` JSON DEFAULT NULL,
  `business_goals` JSON DEFAULT NULL,
  `current_challenges` JSON DEFAULT NULL,
  `onboarding_timeline_expectation` VARCHAR(100) DEFAULT NULL,
  `urgency_level` VARCHAR(50) DEFAULT NULL,
  `expected_growth_rate` VARCHAR(100) DEFAULT NULL,
  
  -- Phase 10: Contact Information & Agreement
  `contact_full_name` VARCHAR(255) DEFAULT NULL,
  `contact_job_title` VARCHAR(255) DEFAULT NULL,
  `contact_email` VARCHAR(255) DEFAULT NULL,
  `contact_phone` VARCHAR(50) DEFAULT NULL,
  `contact_telegram` VARCHAR(100) DEFAULT NULL,
  `contact_whatsapp` VARCHAR(50) DEFAULT NULL,
  `preferred_contact_method` VARCHAR(50) DEFAULT NULL,
  `preferred_contact_time` VARCHAR(100) DEFAULT NULL,
  `preferred_timezone` VARCHAR(100) DEFAULT NULL,
  
  -- Additional Contacts
  `technical_contact` JSON DEFAULT NULL,
  `financial_contact` JSON DEFAULT NULL,
  `compliance_contact` JSON DEFAULT NULL,
  
  -- Legal Agreements
  `terms_accepted` TINYINT(1) DEFAULT 0,
  `privacy_accepted` TINYINT(1) DEFAULT 0,
  `data_processing_consent` TINYINT(1) DEFAULT 0,
  `credit_check_consent` TINYINT(1) DEFAULT 0,
  `marketing_opt_in` TINYINT(1) DEFAULT 0,
  
  -- Additional Information
  `referral_source` VARCHAR(255) DEFAULT NULL,
  `referral_partner` VARCHAR(255) DEFAULT NULL,
  `additional_comments` TEXT DEFAULT NULL,
  
  -- Session metadata
  `ip_address` VARCHAR(50) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `session_data` JSON DEFAULT NULL,
  
  INDEX `idx_hash` (`onboarding_sessions_hash`),
  INDEX `idx_status` (`onboarding_status`),
  INDEX `idx_created` (`datetime_created`),
  INDEX `idx_email` (`contact_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
