-- SQLite-Datenbankschema für Instagram Account Generator

-- Accounts-Tabelle
CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    full_name TEXT,
    created_at TEXT,
    notes TEXT,
    status TEXT DEFAULT 'active',
    proxy_used TEXT,
    metadata TEXT
);

-- Proxy-Nutzungen-Tabelle
CREATE TABLE IF NOT EXISTS proxy_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proxy_type TEXT NOT NULL,
    proxy_string TEXT NOT NULL,
    used_at TEXT NOT NULL,
    success INTEGER DEFAULT 0,
    account_id INTEGER,
    FOREIGN KEY (account_id) REFERENCES accounts (id)
);

-- Fehler-Protokoll-Tabelle
CREATE TABLE IF NOT EXISTS error_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    error_type TEXT NOT NULL,
    error_message TEXT NOT NULL,
    stack_trace TEXT,
    timestamp TEXT NOT NULL,
    account_id INTEGER,
    proxy_used TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts (id)
);

-- Einstellungen-Tabelle
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- SMS-Verifizierungen-Tabelle
CREATE TABLE IF NOT EXISTS sms_verifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone_number TEXT NOT NULL,
    verification_code TEXT,
    service_name TEXT NOT NULL DEFAULT 'instagram',
    timestamp TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    account_id INTEGER,
    FOREIGN KEY (account_id) REFERENCES accounts (id)
);

-- E-Mail-Verifizierungen-Tabelle
CREATE TABLE IF NOT EXISTS email_verifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email_address TEXT NOT NULL,
    verification_code TEXT,
    service_name TEXT NOT NULL DEFAULT 'instagram',
    timestamp TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    account_id INTEGER,
    FOREIGN KEY (account_id) REFERENCES accounts (id)
);

-- Lizenzen-Tabelle
CREATE TABLE IF NOT EXISTS licenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    license_key TEXT NOT NULL UNIQUE,
    activated_at TEXT,
    expires_at TEXT,
    status TEXT DEFAULT 'inactive',
    hardware_id TEXT,
    metadata TEXT
);

-- Nutzungsdaten-Tabelle (für Statistiken)
CREATE TABLE IF NOT EXISTS usage_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action_type TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    success INTEGER DEFAULT 0,
    details TEXT
);

-- Indizes für bessere Performance
CREATE INDEX IF NOT EXISTS idx_accounts_username ON accounts(username);
CREATE INDEX IF NOT EXISTS idx_accounts_email ON accounts(email);
CREATE INDEX IF NOT EXISTS idx_accounts_status ON accounts(status);
CREATE INDEX IF NOT EXISTS idx_accounts_created_at ON accounts(created_at);
CREATE INDEX IF NOT EXISTS idx_proxy_usage_proxy_type ON proxy_usage(proxy_type);
CREATE INDEX IF NOT EXISTS idx_proxy_usage_used_at ON proxy_usage(used_at);
CREATE INDEX IF NOT EXISTS idx_error_logs_error_type ON error_logs(error_type);
CREATE INDEX IF NOT EXISTS idx_error_logs_timestamp ON error_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_sms_verifications_phone ON sms_verifications(phone_number);
CREATE INDEX IF NOT EXISTS idx_email_verifications_email ON email_verifications(email_address);
CREATE INDEX IF NOT EXISTS idx_licenses_key ON licenses(license_key);
CREATE INDEX IF NOT EXISTS idx_usage_stats_action_type ON usage_stats(action_type);
CREATE INDEX IF NOT EXISTS idx_usage_stats_timestamp ON usage_stats(timestamp);

-- Beispieldaten für Testzwecke
INSERT OR IGNORE INTO settings (key, value, updated_at) 
VALUES ('app_version', '1.0.0', datetime('now'));

INSERT OR IGNORE INTO settings (key, value, updated_at) 
VALUES ('last_update_check', datetime('now'), datetime('now'));