SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS push_notifications;
DROP TABLE IF EXISTS support_messages;
DROP TABLE IF EXISTS app_banners;
DROP TABLE IF EXISTS app_pages;
DROP TABLE IF EXISTS api_sync_logs;
DROP TABLE IF EXISTS exness_api_logs;
DROP TABLE IF EXISTS uid_change_logs;
DROP TABLE IF EXISTS cashback_daily_reports;
DROP TABLE IF EXISTS cashback_summary;
DROP TABLE IF EXISTS exness_accounts;
DROP TABLE IF EXISTS user_devices;
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS social_accounts;
DROP TABLE IF EXISTS admin_logs;
DROP TABLE IF EXISTS admin_users;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    public_id VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(120) NULL,
    surname VARCHAR(120) NULL,
    email VARCHAR(180) NULL UNIQUE,
    phone VARCHAR(40) NULL UNIQUE,
    password_hash VARCHAR(255) NULL,

    avatar VARCHAR(255) NULL,
    language VARCHAR(20) DEFAULT 'uz',
    app_theme ENUM('system','light','dark') DEFAULT 'system',

    auth_type ENUM('email','phone','google','apple','x') DEFAULT 'email',

    status ENUM('active','blocked','deleted') DEFAULT 'active',
    block_reason TEXT NULL,

    last_login_at DATETIME NULL,
    email_verified_at DATETIME NULL,
    phone_verified_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_status (status),
    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE social_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,
    provider ENUM('google','apple','x') NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,
    provider_email VARCHAR(180) NULL,
    provider_name VARCHAR(180) NULL,

    raw_profile LONGTEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_provider_user (provider, provider_user_id),
    INDEX idx_social_user (user_id),

    CONSTRAINT fk_social_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL UNIQUE,
    refresh_token_hash VARCHAR(255) NULL UNIQUE,

    device_name VARCHAR(180) NULL,
    device_type ENUM('android','ios','web','unknown') DEFAULT 'unknown',
    ip_address VARCHAR(100) NULL,
    user_agent TEXT NULL,

    is_active TINYINT(1) DEFAULT 1,
    last_used_at DATETIME NULL,
    expires_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sessions_user (user_id),
    INDEX idx_sessions_active (is_active),

    CONSTRAINT fk_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,
    device_id VARCHAR(255) NULL,
    platform ENUM('android','ios','unknown') DEFAULT 'unknown',
    app_version VARCHAR(50) NULL,
    push_token TEXT NULL,

    is_active TINYINT(1) DEFAULT 1,
    last_seen_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_devices_user (user_id),
    INDEX idx_devices_platform (platform),

    CONSTRAINT fk_devices_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exness_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    exness_uid VARCHAR(80) NOT NULL,
    exness_client_id VARCHAR(120) NULL,
    exness_account_number VARCHAR(120) NULL,

    partner_verified TINYINT(1) DEFAULT 0,

    status ENUM(
        'pending',
        'verified',
        'rejected',
        'blocked',
        'unlinked'
    ) DEFAULT 'pending',

    locked TINYINT(1) DEFAULT 1,

    reject_reason TEXT NULL,
    admin_note TEXT NULL,

    first_checked_at DATETIME NULL,
    verified_at DATETIME NULL,
    rejected_at DATETIME NULL,
    unlinked_at DATETIME NULL,

    last_api_check_at DATETIME NULL,
    raw_verify_response LONGTEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_exness_uid (exness_uid),
    UNIQUE KEY unique_user_exness_account (user_id),

    INDEX idx_exness_status (status),
    INDEX idx_exness_partner_verified (partner_verified),
    INDEX idx_exness_uid (exness_uid),

    CONSTRAINT fk_exness_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cashback_daily_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,
    exness_account_id BIGINT UNSIGNED NOT NULL,

    report_date DATE NOT NULL,

    trade_volume DECIMAL(20, 8) DEFAULT 0,
    reward_amount DECIMAL(20, 8) DEFAULT 0,
    cashback_amount DECIMAL(20, 8) DEFAULT 0,

    currency VARCHAR(20) DEFAULT 'USD',

    status ENUM(
        'pending',
        'confirmed',
        'cancelled'
    ) DEFAULT 'pending',

    exness_report_id VARCHAR(180) NULL,
    exness_transaction_id VARCHAR(180) NULL,

    confirmed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    cancel_reason TEXT NULL,

    raw_response LONGTEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_daily_report (exness_account_id, report_date),

    INDEX idx_cashback_user (user_id),
    INDEX idx_cashback_status (status),
    INDEX idx_cashback_date (report_date),

    CONSTRAINT fk_cashback_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_cashback_exness
        FOREIGN KEY (exness_account_id) REFERENCES exness_accounts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cashback_summary (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    exness_account_id BIGINT UNSIGNED NULL,

    total_confirmed_cashback DECIMAL(20, 8) DEFAULT 0,
    total_pending_cashback DECIMAL(20, 8) DEFAULT 0,
    total_cancelled_cashback DECIMAL(20, 8) DEFAULT 0,

    today_cashback DECIMAL(20, 8) DEFAULT 0,
    yesterday_cashback DECIMAL(20, 8) DEFAULT 0,

    weekly_cashback DECIMAL(20, 8) DEFAULT 0,
    monthly_cashback DECIMAL(20, 8) DEFAULT 0,

    monthly_trade_volume DECIMAL(20, 8) DEFAULT 0,
    total_trade_volume DECIMAL(20, 8) DEFAULT 0,

    currency VARCHAR(20) DEFAULT 'USD',

    last_report_date DATE NULL,
    last_sync_at DATETIME NULL,

    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_summary_user (user_id),

    CONSTRAINT fk_summary_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_summary_exness
        FOREIGN KEY (exness_account_id) REFERENCES exness_accounts(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(180) NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,

    full_name VARCHAR(180) NULL,
    role ENUM('super_admin','admin','support') DEFAULT 'admin',

    status ENUM('active','blocked') DEFAULT 'active',

    last_login_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_admin_status (status),
    INDEX idx_admin_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    admin_id BIGINT UNSIGNED NULL,

    action VARCHAR(180) NOT NULL,
    description TEXT NULL,
    target_table VARCHAR(120) NULL,
    target_id BIGINT UNSIGNED NULL,

    ip_address VARCHAR(100) NULL,
    user_agent TEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_admin_logs_admin (admin_id),
    INDEX idx_admin_logs_action (action),
    INDEX idx_admin_logs_target (target_table, target_id),

    CONSTRAINT fk_admin_logs_admin
        FOREIGN KEY (admin_id) REFERENCES admin_users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE uid_change_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    admin_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    exness_account_id BIGINT UNSIGNED NULL,

    action ENUM(
        'unlock',
        'reassign',
        'unlink',
        'manual_verify',
        'manual_reject',
        'block'
    ) NOT NULL,

    old_uid VARCHAR(80) NULL,
    new_uid VARCHAR(80) NULL,

    reason TEXT NULL,
    ip_address VARCHAR(100) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_uid_log_admin (admin_id),
    INDEX idx_uid_log_user (user_id),
    INDEX idx_uid_log_action (action),

    CONSTRAINT fk_uid_log_admin
        FOREIGN KEY (admin_id) REFERENCES admin_users(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_uid_log_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_uid_log_exness
        FOREIGN KEY (exness_account_id) REFERENCES exness_accounts(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_banners (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(180) NOT NULL,
    subtitle VARCHAR(255) NULL,
    image_url VARCHAR(255) NULL,
    action_text VARCHAR(100) NULL,
    action_url VARCHAR(255) NULL,

    position ENUM(
        'home_top',
        'home_middle',
        'onboarding',
        'uid_page',
        'profile',
        'support'
    ) DEFAULT 'home_top',

    background_color VARCHAR(30) DEFAULT '#F6C343',
    text_color VARCHAR(30) DEFAULT '#111111',

    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,

    starts_at DATETIME NULL,
    ends_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_banner_position (position),
    INDEX idx_banner_active (is_active),
    INDEX idx_banner_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    page_key VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(180) NOT NULL,
    content LONGTEXT NULL,

    is_active TINYINT(1) DEFAULT 1,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_pages_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NULL,

    name VARCHAR(150) NULL,
    email VARCHAR(180) NULL,
    subject VARCHAR(180) NULL,
    message TEXT NOT NULL,

    status ENUM('new','read','answered','closed') DEFAULT 'new',
    admin_reply TEXT NULL,
    answered_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_support_user (user_id),
    INDEX idx_support_status (status),

    CONSTRAINT fk_support_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NULL,

    title VARCHAR(180) NOT NULL,
    body TEXT NOT NULL,
    type ENUM(
        'system',
        'cashback_confirmed',
        'uid_verified',
        'uid_rejected',
        'banner',
        'security'
    ) DEFAULT 'system',

    data_payload LONGTEXT NULL,

    is_sent TINYINT(1) DEFAULT 0,
    sent_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_push_user (user_id),
    INDEX idx_push_sent (is_sent),
    INDEX idx_push_type (type),

    CONSTRAINT fk_push_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(120) NOT NULL UNIQUE,
    setting_value LONGTEXT NULL,
    setting_group VARCHAR(80) DEFAULT 'general',
    is_private TINYINT(1) DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_settings_group (setting_group),
    INDEX idx_settings_private (is_private)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exness_api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NULL,
    exness_account_id BIGINT UNSIGNED NULL,

    endpoint VARCHAR(255) NOT NULL,
    request_payload LONGTEXT NULL,
    response_payload LONGTEXT NULL,

    http_status INT NULL,
    api_status ENUM('success','failed') DEFAULT 'success',
    error_message TEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_exness_api_user (user_id),
    INDEX idx_exness_api_account (exness_account_id),
    INDEX idx_exness_api_status (api_status),

    CONSTRAINT fk_exness_api_log_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_exness_api_log_account
        FOREIGN KEY (exness_account_id) REFERENCES exness_accounts(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE api_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    sync_type ENUM(
        'uid_verify',
        'daily_cashback',
        'summary_recalculate',
        'banner_sync',
        'manual_admin_sync'
    ) NOT NULL,

    status ENUM('started','success','failed') DEFAULT 'started',

    total_checked INT DEFAULT 0,
    total_success INT DEFAULT 0,
    total_failed INT DEFAULT 0,

    message TEXT NULL,
    raw_response LONGTEXT NULL,

    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sync_type (sync_type),
    INDEX idx_sync_status (status),
    INDEX idx_sync_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value, setting_group, is_private) VALUES
('app_name', 'CashBack Uz', 'general', 0),
('app_version', '1.0.0', 'general', 0),
('default_language', 'uz', 'general', 0),
('maintenance_mode', '0', 'general', 0),
('maintenance_text', 'Texnik ishlar olib borilmoqda. Iltimos, keyinroq urinib ko‘ring.', 'general', 0),

('support_telegram', '', 'support', 0),
('support_email', '', 'support', 0),

('exness_api_enabled', '0', 'exness', 1),
('exness_api_base_url', '', 'exness', 1),
('exness_api_login', '', 'exness', 1),
('exness_api_password_encrypted', '', 'exness', 1),
('exness_partner_code', '', 'exness', 1),

('uid_auto_verify', '1', 'uid', 0),
('uid_lock_enabled', '1', 'uid', 0),
('uid_manual_unlock_enabled', '1', 'uid', 0),

('cashback_currency', 'USD', 'cashback', 0),
('cashback_daily_sync_enabled', '1', 'cashback', 0),
('cashback_sync_time', '01:00', 'cashback', 0),
('cashback_show_pending', '1', 'cashback', 0),
('cashback_show_cancelled', '1', 'cashback', 0),

('banner_enabled', '1', 'banner', 0),
('push_enabled', '0', 'notification', 0);

INSERT INTO app_pages (page_key, title, content, is_active) VALUES
('onboarding_1', 'CashBack Uz qanday ishlaydi?', 'Exness UID raqamingizni ulang va tasdiqlangan cashbacklaringizni har kuni kuzating.', 1),
('onboarding_2', 'Faqat UID kerak', 'Biz sizdan Exness paroli yoki API kalit so‘ramaymiz. Faqat UID orqali tekshiruv qilinadi.', 1),
('onboarding_3', 'Har kuni avtomatik hisoblanadi', 'Cashbacklar Exness tomonidan tasdiqlangandan so‘ng ilovada avtomatik ko‘rinadi.', 1),
('privacy_policy', 'Maxfiylik siyosati', 'Bu yerga maxfiylik siyosati matni yoziladi.', 1),
('terms', 'Foydalanish shartlari', 'Bu yerga foydalanish shartlari yoziladi.', 1);

INSERT INTO app_banners (
    title,
    subtitle,
    image_url,
    action_text,
    action_url,
    position,
    background_color,
    text_color,
    sort_order,
    is_active
) VALUES
(
    'Exness UID ulang',
    'Cashbacklaringizni har kuni avtomatik kuzating.',
    NULL,
    'UID ulash',
    'app://connect-uid',
    'home_top',
    '#F6C343',
    '#111111',
    1,
    1
),
(
    'Xavfsiz ulanish',
    'Parol yoki API kalit so‘ralmaydi. Faqat UID orqali tekshiruv qilinadi.',
    NULL,
    'Batafsil',
    'app://security',
    'uid_page',
    '#111111',
    '#FFFFFF',
    1,
    1
);

INSERT INTO admin_users (
    username,
    email,
    password_hash,
    full_name,
    role,
    status
) VALUES (
    'admin',
    NULL,
    '$2y$10$replace_this_hash_with_real_password_hash',
    'Main Admin',
    'super_admin',
    'active'
);