SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS management_predictions;
DROP TABLE IF EXISTS management_alerts;
DROP TABLE IF EXISTS executive_snapshots;
DROP TABLE IF EXISTS performance_scores;
DROP TABLE IF EXISTS performance_snapshots;
DROP TABLE IF EXISTS performance_goals;
DROP TABLE IF EXISTS billing_reminders;
DROP TABLE IF EXISTS financial_closures;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS evidence_files;
DROP TABLE IF EXISTS panic_events;
DROP TABLE IF EXISTS delivery_tracking_points;
DROP TABLE IF EXISTS ride_tracking_points;
DROP TABLE IF EXISTS delivery_proofs;
DROP TABLE IF EXISTS forgotten_items;
DROP TABLE IF EXISTS complaints;
DROP TABLE IF EXISTS ratings;
DROP TABLE IF EXISTS agent_commissions;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS delivery_requests;
DROP TABLE IF EXISTS ride_requests;
DROP TABLE IF EXISTS company_wallet_settings;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS passengers;
DROP TABLE IF EXISTS driver_documents;
DROP TABLE IF EXISTS driver_subscriptions;
DROP TABLE IF EXISTS drivers;
DROP TABLE IF EXISTS agents;
DROP TABLE IF EXISTS support_tickets;
DROP TABLE IF EXISTS platform_settings;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NULL,
    cpf VARCHAR(20) NULL,
    cnpj VARCHAR(25) NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('pending','active','approved','suspended','blocked') NOT NULL DEFAULT 'pending',
    email_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    remember_token VARCHAR(100) NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE platform_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_platform_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    opened_by_user_id BIGINT UNSIGNED NOT NULL,
    assigned_to_user_id BIGINT UNSIGNED NULL,
    target_type ENUM('motorista','passageiro','empresa','agente','plataforma') NOT NULL DEFAULT 'plataforma',
    subject VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status ENUM('open','in_progress','waiting','resolved','closed') NOT NULL DEFAULT 'open',
    resolution_notes TEXT NULL,
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_support_tickets_opened_by FOREIGN KEY (opened_by_user_id) REFERENCES users(id),
    CONSTRAINT fk_support_tickets_assigned_to FOREIGN KEY (assigned_to_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE agents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    credential_number VARCHAR(50) NOT NULL,
    credential_expires_at DATE NULL,
    registration_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    annual_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    commission_percent DECIMAL(8,2) NOT NULL DEFAULT 60.00,
    agent_status ENUM('pending','active','expired','suspended','blocked') NOT NULL DEFAULT 'pending',
    region_name VARCHAR(150) NULL,
    notes TEXT NULL,
    qr_code_path VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_agents_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_agents_user_id (user_id),
    UNIQUE KEY uq_agents_credential_number (credential_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE drivers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    agent_id BIGINT UNSIGNED NULL,
    driver_type ENUM('passageiro','entregador','misto') NOT NULL DEFAULT 'misto',
    driver_status ENUM('pending','approved','suspended','blocked','banned') NOT NULL DEFAULT 'pending',
    in_person_validated TINYINT(1) NOT NULL DEFAULT 0,
    validated_at DATETIME NULL,
    validated_by_user_id BIGINT UNSIGNED NULL,
    birth_date DATE NULL,
    cnh_number VARCHAR(50) NULL,
    cnh_category VARCHAR(10) NULL,
    cnh_expires_at DATE NULL,
    vehicle_brand VARCHAR(100) NULL,
    vehicle_model VARCHAR(100) NULL,
    vehicle_year VARCHAR(10) NULL,
    vehicle_color VARCHAR(50) NULL,
    vehicle_plate VARCHAR(20) NULL,
    vehicle_type VARCHAR(50) NULL,
    vehicle_renavam VARCHAR(50) NULL,
    pix_key_type VARCHAR(30) NULL,
    pix_key_value VARCHAR(150) NULL,
    bank_name VARCHAR(100) NULL,
    bank_agency VARCHAR(30) NULL,
    bank_account VARCHAR(50) NULL,
    bank_account_type VARCHAR(30) NULL,
    total_rides INT NOT NULL DEFAULT 0,
    total_deliveries INT NOT NULL DEFAULT 0,
    average_rating DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_drivers_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_drivers_agent FOREIGN KEY (agent_id) REFERENCES agents(id),
    CONSTRAINT fk_drivers_validated_by FOREIGN KEY (validated_by_user_id) REFERENCES users(id),
    UNIQUE KEY uq_drivers_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE driver_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT UNSIGNED NOT NULL,
    plan_name VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 120.00,
    billing_cycle ENUM('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
    status ENUM('active','late','suspended','cancelled') NOT NULL DEFAULT 'active',
    started_at DATE NOT NULL,
    next_due_at DATE NOT NULL,
    cancelled_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_driver_subscriptions_driver FOREIGN KEY (driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE driver_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT UNSIGNED NOT NULL,
    document_type ENUM('cnh','rg','cpf','comprovante_endereco','crlv','seguro','foto_perfil','vistoria','antecedente','outro') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    verified TINYINT(1) NOT NULL DEFAULT 0,
    verified_by BIGINT UNSIGNED NULL,
    verified_at DATETIME NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_driver_documents_driver FOREIGN KEY (driver_id) REFERENCES drivers(id),
    CONSTRAINT fk_driver_documents_verified_by FOREIGN KEY (verified_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE passengers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    preferred_payment_method ENUM('pix','credito','debito','dinheiro','outro') NULL,
    passenger_status ENUM('active','suspended','blocked','banned') NOT NULL DEFAULT 'active',
    average_rating DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_passengers_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_passengers_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    trade_name VARCHAR(150) NOT NULL,
    legal_name VARCHAR(200) NOT NULL,
    municipal_registration VARCHAR(100) NULL,
    address_zipcode VARCHAR(20) NOT NULL,
    address_street VARCHAR(200) NOT NULL,
    address_number VARCHAR(20) NOT NULL,
    address_complement VARCHAR(100) NULL,
    address_district VARCHAR(100) NOT NULL,
    address_city VARCHAR(100) NOT NULL,
    address_state VARCHAR(10) NOT NULL,
    contact_name VARCHAR(150) NOT NULL,
    company_status ENUM('pending','active','suspended','blocked') NOT NULL DEFAULT 'pending',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_companies_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_companies_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE company_wallet_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    accepts_pix TINYINT(1) NOT NULL DEFAULT 1,
    accepts_credit TINYINT(1) NOT NULL DEFAULT 1,
    accepts_debit TINYINT(1) NOT NULL DEFAULT 1,
    accepts_invoice TINYINT(1) NOT NULL DEFAULT 0,
    default_fee_percent DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_company_wallet_settings_company FOREIGN KEY (company_id) REFERENCES companies(id),
    UNIQUE KEY uq_company_wallet_settings_company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ride_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    passenger_id BIGINT UNSIGNED NOT NULL,
    assigned_driver_id BIGINT UNSIGNED NULL,
    origin_address VARCHAR(255) NOT NULL,
    origin_lat DECIMAL(10,7) NULL,
    origin_lng DECIMAL(10,7) NULL,
    destination_address VARCHAR(255) NOT NULL,
    destination_lat DECIMAL(10,7) NULL,
    destination_lng DECIMAL(10,7) NULL,
    stop_address VARCHAR(255) NULL,
    stop_lat DECIMAL(10,7) NULL,
    stop_lng DECIMAL(10,7) NULL,
    estimated_distance_km DECIMAL(10,2) NULL,
    estimated_duration_minutes INT NULL,
    estimated_price DECIMAL(12,2) NULL,
    payment_method ENUM('pix','credito','debito','dinheiro','outro') NOT NULL,
    payment_status ENUM('pending','confirmed','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
    ride_status ENUM('requested','accepted','in_progress','completed','cancelled','disputed') NOT NULL DEFAULT 'requested',
    requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    accepted_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    cancelled_by ENUM('passageiro','motorista','admin') NULL,
    cancellation_reason TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ride_requests_passenger FOREIGN KEY (passenger_id) REFERENCES passengers(id),
    CONSTRAINT fk_ride_requests_driver FOREIGN KEY (assigned_driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE delivery_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_type ENUM('empresa','manual') NOT NULL DEFAULT 'empresa',
    company_id BIGINT UNSIGNED NULL,
    requester_user_id BIGINT UNSIGNED NOT NULL,
    assigned_driver_id BIGINT UNSIGNED NULL,
    pickup_address VARCHAR(255) NOT NULL,
    pickup_lat DECIMAL(10,7) NULL,
    pickup_lng DECIMAL(10,7) NULL,
    destination_address VARCHAR(255) NOT NULL,
    destination_lat DECIMAL(10,7) NULL,
    destination_lng DECIMAL(10,7) NULL,
    recipient_name VARCHAR(150) NULL,
    recipient_phone VARCHAR(30) NULL,
    package_type VARCHAR(100) NOT NULL,
    package_description TEXT NULL,
    package_value DECIMAL(12,2) NULL,
    proof_required TINYINT(1) NOT NULL DEFAULT 1,
    payment_method ENUM('pix','credito','debito','faturado','dinheiro','outro') NOT NULL,
    payment_status ENUM('pending','confirmed','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
    delivery_status ENUM('requested','accepted','picked_up','in_route','delivered','cancelled','disputed') NOT NULL DEFAULT 'requested',
    requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    accepted_at DATETIME NULL,
    picked_up_at DATETIME NULL,
    delivered_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_delivery_requests_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_delivery_requests_requester_user FOREIGN KEY (requester_user_id) REFERENCES users(id),
    CONSTRAINT fk_delivery_requests_driver FOREIGN KEY (assigned_driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payer_user_id BIGINT UNSIGNED NOT NULL,
    payee_user_id BIGINT UNSIGNED NULL,
    related_type VARCHAR(50) NOT NULL,
    related_id BIGINT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    payment_method ENUM('pix','credito','debito','boleto','dinheiro','faturado','outro') NOT NULL,
    status ENUM('pending','confirmed','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
    external_reference VARCHAR(150) NULL,
    paid_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_payments_payer FOREIGN KEY (payer_user_id) REFERENCES users(id),
    CONSTRAINT fk_payments_payee FOREIGN KEY (payee_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE agent_commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agent_id BIGINT UNSIGNED NOT NULL,
    wallet_client_id BIGINT UNSIGNED NULL,
    reference_type VARCHAR(50) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    commission_percent DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    commission_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    due_date DATE NULL,
    status ENUM('pending','approved','blocked','paid','cancelled') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_agent_commissions_agent FOREIGN KEY (agent_id) REFERENCES agents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ratings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED NOT NULL,
    context_type ENUM('corrida','entrega','agente','suporte') NOT NULL,
    context_id BIGINT UNSIGNED NOT NULL,
    score_cordiality TINYINT UNSIGNED NULL,
    score_punctuality TINYINT UNSIGNED NULL,
    score_safety TINYINT UNSIGNED NULL,
    score_cleanliness TINYINT UNSIGNED NULL,
    score_reliability TINYINT UNSIGNED NULL,
    overall_score TINYINT UNSIGNED NOT NULL,
    comment TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ratings_from_user FOREIGN KEY (from_user_id) REFERENCES users(id),
    CONSTRAINT fk_ratings_to_user FOREIGN KEY (to_user_id) REFERENCES users(id),
    UNIQUE KEY uq_ratings_context_unique (from_user_id, to_user_id, context_type, context_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE complaints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reported_by_user_id BIGINT UNSIGNED NOT NULL,
    target_user_id BIGINT UNSIGNED NOT NULL,
    related_type ENUM('corrida','entrega','agente','suporte','outro') NOT NULL,
    related_id BIGINT UNSIGNED NULL,
    complaint_type ENUM('fraude','golpe','abuso','ameaca','veiculo_irregular','pagamento','mau_comportamento','falsa_denuncia','outro') NOT NULL,
    description TEXT NOT NULL,
    status ENUM('open','investigating','substantiated','dismissed','resolved') NOT NULL DEFAULT 'open',
    decision TEXT NULL,
    handled_by_user_id BIGINT UNSIGNED NULL,
    handled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_complaints_reported_by FOREIGN KEY (reported_by_user_id) REFERENCES users(id),
    CONSTRAINT fk_complaints_target_user FOREIGN KEY (target_user_id) REFERENCES users(id),
    CONSTRAINT fk_complaints_handled_by FOREIGN KEY (handled_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forgotten_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_request_id BIGINT UNSIGNED NOT NULL,
    passenger_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    description TEXT NOT NULL,
    status ENUM('reported','driver_contacted','found','returned','not_found','closed') NOT NULL DEFAULT 'reported',
    notes TEXT NULL,
    reported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_forgotten_items_ride FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id),
    CONSTRAINT fk_forgotten_items_passenger FOREIGN KEY (passenger_id) REFERENCES passengers(id),
    CONSTRAINT fk_forgotten_items_driver FOREIGN KEY (driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE delivery_proofs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    delivery_request_id BIGINT UNSIGNED NOT NULL,
    proof_type ENUM('photo','signature','document') NOT NULL DEFAULT 'photo',
    file_path VARCHAR(255) NULL,
    typed_signature_name VARCHAR(150) NULL,
    receiver_document VARCHAR(50) NULL,
    notes TEXT NULL,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_delivery_proofs_delivery FOREIGN KEY (delivery_request_id) REFERENCES delivery_requests(id),
    CONSTRAINT fk_delivery_proofs_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ride_tracking_points (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_request_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    lat DECIMAL(10,7) NOT NULL,
    lng DECIMAL(10,7) NOT NULL,
    speed_kmh DECIMAL(8,2) NULL,
    heading DECIMAL(8,2) NULL,
    recorded_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ride_tracking_points_ride FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id),
    CONSTRAINT fk_ride_tracking_points_driver FOREIGN KEY (driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE delivery_tracking_points (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    delivery_request_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    lat DECIMAL(10,7) NOT NULL,
    lng DECIMAL(10,7) NOT NULL,
    speed_kmh DECIMAL(8,2) NULL,
    heading DECIMAL(8,2) NULL,
    recorded_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_delivery_tracking_points_delivery FOREIGN KEY (delivery_request_id) REFERENCES delivery_requests(id),
    CONSTRAINT fk_delivery_tracking_points_driver FOREIGN KEY (driver_id) REFERENCES drivers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE panic_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    triggered_by_user_id BIGINT UNSIGNED NOT NULL,
    related_type ENUM('corrida','entrega','manual') NOT NULL DEFAULT 'manual',
    related_id BIGINT UNSIGNED NULL,
    lat DECIMAL(10,7) NOT NULL,
    lng DECIMAL(10,7) NOT NULL,
    status ENUM('triggered','acknowledged','forwarded','closed') NOT NULL DEFAULT 'triggered',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_panic_events_triggered_by FOREIGN KEY (triggered_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evidence_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    related_type ENUM('complaint','ticket','panic','corrida','entrega','agente') NOT NULL,
    related_id BIGINT UNSIGNED NOT NULL,
    file_type ENUM('image','audio','video','document') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_evidence_files_uploaded_by FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE financial_closures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_month CHAR(7) NOT NULL,
    total_payments_gross DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_payments_net DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_driver_subscriptions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_agent_commissions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_rides_completed INT NOT NULL DEFAULT 0,
    total_deliveries_completed INT NOT NULL DEFAULT 0,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    closed_by_user_id BIGINT UNSIGNED NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_financial_closures_user FOREIGN KEY (closed_by_user_id) REFERENCES users(id),
    UNIQUE KEY uq_financial_closure_month (reference_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE billing_reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_subscription_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reminder_type ENUM('upcoming','due_today','late_1','late_3','late_7','late_15','suspended') NOT NULL,
    channel ENUM('internal','email','whatsapp') NOT NULL DEFAULT 'internal',
    message TEXT NOT NULL,
    status ENUM('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
    scheduled_for DATETIME NOT NULL,
    sent_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_billing_reminders_subscription FOREIGN KEY (driver_subscription_id) REFERENCES driver_subscriptions(id),
    CONSTRAINT fk_billing_reminders_driver FOREIGN KEY (driver_id) REFERENCES drivers(id),
    CONSTRAINT fk_billing_reminders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE performance_goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_type ENUM('agente','motorista','empresa','plataforma') NOT NULL,
    target_user_id BIGINT UNSIGNED NULL,
    reference_month CHAR(7) NOT NULL,
    target_metric VARCHAR(50) NOT NULL,
    target_value DECIMAL(12,2) NOT NULL,
    notes TEXT NULL,
    status ENUM('open','achieved','missed','cancelled') NOT NULL DEFAULT 'open',
    created_by_user_id BIGINT UNSIGNED NULL,
    achieved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_performance_goals_target_user FOREIGN KEY (target_user_id) REFERENCES users(id),
    CONSTRAINT fk_performance_goals_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE performance_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    snapshot_month CHAR(7) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    entity_type ENUM('agente','motorista','plataforma') NOT NULL,
    total_rides INT NOT NULL DEFAULT 0,
    total_deliveries INT NOT NULL DEFAULT 0,
    total_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_commissions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_subscriptions_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_clients INT NOT NULL DEFAULT 0,
    rating_avg DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_performance_snapshots_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_snapshot_entity_month (snapshot_month, user_id, entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE performance_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    entity_type ENUM('agente','motorista') NOT NULL,
    reference_month CHAR(7) NOT NULL,
    score_productivity DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    score_revenue DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    score_quality DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    score_retention DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    score_risk DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    final_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    classification ENUM('excelente','bom','regular','critico') NOT NULL DEFAULT 'regular',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_performance_scores_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_performance_scores_user_month_type (user_id, entity_type, reference_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE executive_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_month CHAR(7) NOT NULL,
    total_new_drivers INT NOT NULL DEFAULT 0,
    total_new_passengers INT NOT NULL DEFAULT 0,
    total_new_companies INT NOT NULL DEFAULT 0,
    total_active_drivers INT NOT NULL DEFAULT 0,
    total_late_subscriptions INT NOT NULL DEFAULT 0,
    total_completed_rides INT NOT NULL DEFAULT 0,
    total_completed_deliveries INT NOT NULL DEFAULT 0,
    gross_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    commissions_generated DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    average_driver_rating DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_executive_snapshot_month (reference_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE management_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alert_type ENUM('performance_drop','financial_risk','critical_default','churn_risk','low_rating','low_conversion','agent_underperformance','driver_underperformance') NOT NULL,
    entity_type ENUM('agente','motorista','empresa','plataforma') NOT NULL,
    target_user_id BIGINT UNSIGNED NULL,
    reference_month CHAR(7) NOT NULL,
    severity ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    recommended_action TEXT NULL,
    status ENUM('open','acknowledged','resolved','dismissed') NOT NULL DEFAULT 'open',
    metadata_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    resolved_by_user_id BIGINT UNSIGNED NULL,
    CONSTRAINT fk_management_alerts_target_user FOREIGN KEY (target_user_id) REFERENCES users(id),
    CONSTRAINT fk_management_alerts_resolved_by FOREIGN KEY (resolved_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE management_predictions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prediction_type ENUM('driver_churn','agent_drop','default_risk','growth_projection') NOT NULL,
    entity_type ENUM('agente','motorista','plataforma') NOT NULL,
    target_user_id BIGINT UNSIGNED NULL,
    reference_month CHAR(7) NOT NULL,
    probability_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    predicted_value DECIMAL(12,2) NULL,
    reasoning TEXT NULL,
    status ENUM('active','confirmed','dismissed','expired') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_management_predictions_target_user FOREIGN KEY (target_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (name, label) VALUES
('admin_master', 'Administrador Master'),
('supervisor', 'Supervisor'),
('agente', 'Agente'),
('motorista_passageiro', 'Motorista Passageiro'),
('motorista_entregador', 'Motorista Entregador'),
('motorista_misto', 'Motorista Misto'),
('passageiro', 'Passageiro'),
('empresa', 'Empresa');

INSERT INTO users (role_id, name, email, phone, password_hash, status, email_verified_at)
VALUES (
    (SELECT id FROM roles WHERE name = 'admin_master' LIMIT 1),
    'Administrador ZUBBEN',
    'admin@zubben.com.br',
    '67999999999',
    '$2y$10$wD0YQ8w8B5nYh2fSkY0CbOKPqjY4kD9M3x1l0vQ0Qh7s7xWm0m2Q6',
    'active',
    NOW()
);

INSERT INTO platform_settings (setting_key, setting_value) VALUES
('app_name', 'ZUBBEN'),
('app_timezone', 'America/Sao_Paulo'),
('currency', 'BRL'),
('country', 'Brasil'),
('default_driver_subscription_amount', '120.00'),
('default_agent_commission_percent', '60.00'),
('support_email_driver', 'sac.motora@criacaodesitesms.com.br'),
('support_email_passenger', 'sac.passageiro@criacaodesitesms.com.br'),
('support_phone_1', '67 99202-1377'),
('support_phone_2', '67 98106-4440'),
('support_phone_3', '67 3222-9253'),
('gps_precision_mode', 'high'),
('installation_status', 'installed');