PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS supports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    email TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS mailers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    email TEXT,
    commission_rate REAL DEFAULT 5,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('admin', 'support', 'mailer')),
    support_id INTEGER,
    mailer_id INTEGER,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (support_id) REFERENCES supports(id),
    FOREIGN KEY (mailer_id) REFERENCES mailers(id)
);

CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    phone TEXT,
    country TEXT,
    website TEXT CHECK(website IN ('king', 'trex', 'xtream')),
    support_id INTEGER,
    mailer_id INTEGER,
    tracking_token TEXT,
    visitor_id TEXT,
    source_user_id TEXT,
    source_list_id TEXT,
    source_email_id TEXT,
    source_campaign_id TEXT,
    first_click_at TEXT,
    last_click_at TEXT,
    user_agent TEXT,
    ip_address TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (support_id) REFERENCES supports(id),
    FOREIGN KEY (mailer_id) REFERENCES mailers(id)
);

CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,

    service_type TEXT NOT NULL CHECK(service_type IN ('test', 'subscription')),
    subscription_source TEXT CHECK(subscription_source IN ('direct', 'from_test', 'renewal')),

    previous_service_id INTEGER,
    converted_from_service_id INTEGER,

    panel_name TEXT CHECK(panel_name IN (
        'medamirpro',
        'strongpro',
        'strongpro2',
        'strongpro3',
        'mascoder',
        'mourabit',
        'mourabit2',
        'tivione',
        'eagle4k'
    )),
    website TEXT CHECK(website IN ('king', 'trex', 'xtream')),

    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'expired', 'cancelled')),

    subscription_period TEXT CHECK(subscription_period IN (
        '1_month',
        '3_months',
        '6_months',
        '1_year',
        '2_years',
        '3_years',
        '5_years'
    )),

    device_count INTEGER NOT NULL DEFAULT 1 CHECK(device_count IN (1, 2, 3)),

    start_date TEXT NOT NULL,
    expiration_date TEXT NOT NULL,

    reminder_sent INTEGER DEFAULT 0,
    followup_enabled INTEGER DEFAULT 1,
    post_4_followup_mode TEXT DEFAULT 'disabled' CHECK(post_4_followup_mode IN ('disabled', 'daily', 'gap')),

    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (previous_service_id) REFERENCES services(id),
    FOREIGN KEY (converted_from_service_id) REFERENCES services(id)
);

CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    service_id INTEGER,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'USD',
    payment_method TEXT,
    payment_status TEXT NOT NULL DEFAULT 'pending' CHECK(payment_status IN ('pending', 'completed', 'refunded')),
    payment_date TEXT,
    external_order_id TEXT,
    refund_date TEXT,
    notes TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
);

CREATE TABLE IF NOT EXISTS charges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    category TEXT NOT NULL CHECK(category IN ('mailer_payout', 'iptv_panel_credit', 'hosting_vps', 'domain', 'other')),
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'USD',
    charge_type TEXT NOT NULL CHECK(charge_type IN ('monthly', 'yearly', 'one_time')),
    charge_date TEXT NOT NULL,
    notes TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tracking_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    event_type TEXT NOT NULL CHECK(event_type IN (
        'landing_click',
        'free_trial_submit',
        'buy_now_click',
        'checkout_start',
        'purchase_completed',
        'refund'
    )),

    tracking_token TEXT,
    visitor_id TEXT,

    mailer_id INTEGER,
    source_user_id TEXT,
    source_list_id TEXT,
    source_email_id TEXT,
    source_campaign_id TEXT,

    client_id INTEGER,
    service_id INTEGER,
    payment_id INTEGER,

    amount REAL,
    currency TEXT DEFAULT 'USD',

    page_url TEXT,
    referrer TEXT,
    ip_address TEXT,
    user_agent TEXT,

    is_unique INTEGER DEFAULT 0,
    is_duplicate INTEGER DEFAULT 0,
    is_bot INTEGER DEFAULT 0,
    is_proxy INTEGER DEFAULT 0,
    is_suspicious INTEGER DEFAULT 0,
    is_valid INTEGER DEFAULT 1,

    invalid_reason TEXT,

    event_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (mailer_id) REFERENCES mailers(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (payment_id) REFERENCES payments(id)
);

CREATE TABLE IF NOT EXISTS click_audits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tracking_event_id INTEGER NOT NULL,

    check_type TEXT NOT NULL,
    result TEXT NOT NULL CHECK(result IN ('passed', 'failed', 'warning')),
    reason TEXT,
    score INTEGER DEFAULT 0,

    checked_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (tracking_event_id) REFERENCES tracking_events(id)
);

CREATE TABLE IF NOT EXISTS conversions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER,
    service_id INTEGER,
    payment_id INTEGER,
    mailer_id INTEGER,

    tracking_token TEXT,
    visitor_id TEXT,

    source_user_id TEXT,
    source_list_id TEXT,
    source_email_id TEXT,
    source_campaign_id TEXT,

    conversion_status TEXT DEFAULT 'completed' CHECK(conversion_status IN ('pending', 'completed', 'refunded')),

    conversion_amount REAL NOT NULL,
    currency TEXT DEFAULT 'USD',

    commission_rate REAL DEFAULT 5,
    commission_amount REAL DEFAULT 0,

    converted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    refunded_at TEXT,

    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (payment_id) REFERENCES payments(id),
    FOREIGN KEY (mailer_id) REFERENCES mailers(id)
);

CREATE TABLE IF NOT EXISTS commissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mailer_id INTEGER NOT NULL,
    conversion_id INTEGER NOT NULL,
    payment_id INTEGER,

    amount REAL NOT NULL,
    commission_rate REAL NOT NULL,

    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'paid', 'cancelled')),

    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    paid_at TEXT,

    FOREIGN KEY (mailer_id) REFERENCES mailers(id),
    FOREIGN KEY (conversion_id) REFERENCES conversions(id),
    FOREIGN KEY (payment_id) REFERENCES payments(id)
);

CREATE TABLE IF NOT EXISTS reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,

    reminder_type TEXT NOT NULL CHECK(reminder_type IN ('test_expiration', 'subscription_renewal')),
    reminder_step INTEGER NOT NULL DEFAULT 1,

    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'stopped')),

    scheduled_at TEXT,
    sent_at TEXT,
    error_message TEXT,

    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
);

CREATE TABLE IF NOT EXISTS email_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER,
    service_id INTEGER,

    email_to TEXT NOT NULL,
    subject TEXT NOT NULL,
    email_type TEXT,
    campaign_type TEXT,

    reminder_step INTEGER,
    client_type_snapshot TEXT,
    website_snapshot TEXT,

    status TEXT NOT NULL CHECK(status IN ('sent', 'failed')),
    error_message TEXT,

    sent_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
);

CREATE TABLE IF NOT EXISTS client_notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    user_id INTEGER,
    note TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_clients_email ON clients(email);
CREATE INDEX IF NOT EXISTS idx_clients_support_id ON clients(support_id);
CREATE INDEX IF NOT EXISTS idx_clients_mailer_id ON clients(mailer_id);

CREATE INDEX IF NOT EXISTS idx_services_client_id ON services(client_id);
CREATE INDEX IF NOT EXISTS idx_services_type ON services(service_type);
CREATE INDEX IF NOT EXISTS idx_services_status ON services(status);
CREATE INDEX IF NOT EXISTS idx_services_expiration_date ON services(expiration_date);

CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(payment_status);
CREATE INDEX IF NOT EXISTS idx_payments_payment_date ON payments(payment_date);

CREATE INDEX IF NOT EXISTS idx_tracking_mailer_id ON tracking_events(mailer_id);
CREATE INDEX IF NOT EXISTS idx_tracking_event_type ON tracking_events(event_type);
CREATE INDEX IF NOT EXISTS idx_tracking_event_at ON tracking_events(event_at);
CREATE INDEX IF NOT EXISTS idx_tracking_visitor_id ON tracking_events(visitor_id);
CREATE INDEX IF NOT EXISTS idx_tracking_token ON tracking_events(tracking_token);

CREATE INDEX IF NOT EXISTS idx_conversions_mailer_id ON conversions(mailer_id);
CREATE INDEX IF NOT EXISTS idx_conversions_status ON conversions(conversion_status);
CREATE INDEX IF NOT EXISTS idx_conversions_converted_at ON conversions(converted_at);

CREATE INDEX IF NOT EXISTS idx_commissions_mailer_id ON commissions(mailer_id);
CREATE INDEX IF NOT EXISTS idx_commissions_status ON commissions(status);
