<?php
// OneLauncher - db.php (SQLite Version)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

date_default_timezone_set('America/Sao_Paulo');

// Nome do arquivo do banco de dados SQLite
$db_file = __DIR__ . '/onelauncher.db';

try {
    // Conexão via PDO
    $pdo = new PDO("sqlite:" . $db_file);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("ERRO DE CONEXÃO SQLITE: " . $e->getMessage());
}

/**
 * Classe de Ponte para manter compatibilidade com o código original (MySQLi)
 */
class SQLiteBridge {
    private $pdo;
    public $insert_id = 0;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    private function translate_sql($sql) {
        $sql = str_ireplace('INT AUTO_INCREMENT PRIMARY KEY', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);
        $sql = str_ireplace('int(11) NOT NULL AUTO_INCREMENT', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);
        $sql = str_ireplace('PRIMARY KEY AUTO_INCREMENT', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);
        $sql = str_ireplace('ENGINE=InnoDB', '', $sql);
        $sql = str_ireplace('DEFAULT CHARSET=utf8', '', $sql);
        $sql = str_ireplace('INT UNSIGNED', 'INTEGER', $sql);
        $sql = str_ireplace('UNIQUE KEY', 'UNIQUE', $sql);
        $sql = str_ireplace('INSERT IGNORE', 'INSERT OR IGNORE', $sql);
        return $sql;
    }

    public function query($sql) {
        try {
            $sql = $this->translate_sql($sql);
            
            // SHOW COLUMNS workaround
            if (stripos($sql, 'SHOW COLUMNS FROM') !== false) {
                preg_match("/FROM `?(\w+)`? LIKE '(\w+)'/i", $sql, $matches);
                if (count($matches) >= 3) {
                    $table = $matches[1];
                    $col = $matches[2];
                    $stmt = $this->pdo->query("PRAGMA table_info($table)");
                    $cols = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $exists = false;
                    foreach ($cols as $c) { if ($c['name'] == $col) $exists = true; }
                    return new SQLiteBridgeResult(null, $exists ? 1 : 0);
                }
            }

            $stmt = $this->pdo->query($sql);
            $this->insert_id = $this->pdo->lastInsertId();
            
            if (stripos(trim($sql), 'SELECT') === 0 && $stmt) {
                $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                return new SQLiteBridgeResult($data, count($data));
            }
            return new SQLiteBridgeResult(null, 0);
        } catch (PDOException $e) {
            return false;
        }
    }

    public function prepare($sql) {
        $sql = $this->translate_sql($sql);
        try {
            $stmt = $this->pdo->prepare($sql);
            if ($stmt === false) {
                error_log("[OneLauncher DB] prepare() retornou false para: " . $sql);
                return false;
            }
            return new SQLiteBridgeStatement($stmt);
        } catch (PDOException $e) {
            error_log("[OneLauncher DB] PDOException em prepare(): " . $e->getMessage() . " | SQL: " . $sql);
            return false;
        }
    }

    public function select_db($name) { return true; }
    public function set_charset($charset) { return true; }
}

class SQLiteBridgeStatement {
    private $stmt;
    private $params = [];
    private $resultData = null;

    public function __construct($stmt) {
        $this->stmt = $stmt;
    }

    public function bind_param($types, ...$vars) {
        // Ignora tipos (s, i, d, b) pois PDO infere
        $this->params = $vars;
        return true;
    }

    public function execute() {
        try {
            // MySQLi bind_param usa referências, mas o spread ...$vars no PHP moderno ajuda
            return $this->stmt->execute($this->params);
        } catch (PDOException $e) {
            return false;
        }
    }

    public function get_result() {
        $data = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
        return new SQLiteBridgeResult($data, count($data));
    }
    
    public function close() { return true; }
}

class SQLiteBridgeResult {
    private $data;
    private $index = 0;
    public $num_rows = 0;

    public function __construct($data, $num_rows) {
        $this->data = $data;
        $this->num_rows = $num_rows;
    }

    public function fetch_assoc() {
        if ($this->data === null || $this->index >= count($this->data)) return null;
        return $this->data[$this->index++];
    }
    
    public function free() { $this->data = null; }
}

// Instancia a ponte de compatibilidade
$conn = new SQLiteBridge($pdo);

// --- Inicialização do Banco (Múltiplos Temas e Customizações) ---

$conn->query("CREATE TABLE IF NOT EXISTS `themes` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
)");

$res = $conn->query("SELECT id FROM themes LIMIT 1");
if (!$res || $res->num_rows == 0) {
    $conn->query("INSERT INTO themes (id, name) VALUES (1, 'Tema Padrão')");
}

$conn->query("CREATE TABLE IF NOT EXISTS layout_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    theme_id INTEGER DEFAULT 1,
    component_name VARCHAR(50),
    margin_top INT DEFAULT 0,
    margin_left INT DEFAULT 0,
    margin_right INT DEFAULT 0,
    margin_bottom INT DEFAULT 0,
    width_weight FLOAT DEFAULT 1.0,
    height_weight FLOAT DEFAULT 1.0,
    item_width INT DEFAULT 0,
    item_height INT DEFAULT 0,
    corner_radius INT DEFAULT 16,
    spacing INT DEFAULT 16,
    x FLOAT DEFAULT 0.0,
    y FLOAT DEFAULT 0.0,
    width FLOAT DEFAULT 0.0,
    height FLOAT DEFAULT 0.0,
    visible TINYINT DEFAULT 1,
    active TINYINT DEFAULT 1,
    UNIQUE(theme_id, component_name)
)");

// Migrações preventivas para adicionar as novas colunas em bancos existentes
$conn->query("ALTER TABLE layout_settings ADD COLUMN x FLOAT DEFAULT 0.0");
$conn->query("ALTER TABLE layout_settings ADD COLUMN y FLOAT DEFAULT 0.0");
$conn->query("ALTER TABLE layout_settings ADD COLUMN width FLOAT DEFAULT 0.0");
$conn->query("ALTER TABLE layout_settings ADD COLUMN height FLOAT DEFAULT 0.0");
$conn->query("ALTER TABLE layout_settings ADD COLUMN visible TINYINT DEFAULT 1");
$conn->query("ALTER TABLE layout_settings ADD COLUMN theme_id INTEGER DEFAULT 1");
// Nota: Em SQLite, adicionar UNIQUE após a criação requer recriar a tabela, mas usaremos REPLACE na aplicação.

$components = ['logo', 'clock_area', 'propaganda', 'vertical_buttons', 'apps_grid'];
foreach ($components as $comp) {
    $conn->query("INSERT IGNORE INTO layout_settings (theme_id, component_name) VALUES (1, '$comp')");
}

$conn->query("CREATE TABLE IF NOT EXISTS `admins` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `username` varchar(50) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role` varchar(20) DEFAULT 'master',
  `app_token` varchar(64) DEFAULT NULL
)");

$res = $conn->query("SELECT id FROM admins LIMIT 1");
if (!$res || $res->num_rows == 0) {
    $passHash = password_hash('admin123', PASSWORD_DEFAULT);
    $conn->query("INSERT OR IGNORE INTO admins (username, password, role, app_token) VALUES ('admin', '$passHash', 'superadmin', 'onelauncher_token_default')");
}

$conn->query("CREATE TABLE IF NOT EXISTS `config` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `app_name` varchar(100) DEFAULT 'OneLauncher',
  `app_logo` varchar(255) DEFAULT '',
  `app_background` varchar(255) DEFAULT '',
  `primary_color` varchar(20) DEFAULT '#00008B',
  `update_version_code` int DEFAULT 1,
  `update_apk_url` varchar(255) DEFAULT '',
  `custom_layout_enabled` TINYINT(1) DEFAULT 1,
  `mercado_pago_token` text DEFAULT '',
  `tech_password` text DEFAULT '8899',
  UNIQUE(theme_id)
)");

$conn->query("ALTER TABLE config ADD COLUMN theme_id INTEGER DEFAULT 1");
$conn->query("ALTER TABLE config ADD COLUMN panel_title VARCHAR(100) DEFAULT 'Painel Admin'");
$conn->query("ALTER TABLE config ADD COLUMN panel_logo_size INTEGER DEFAULT 42");

$res = $conn->query("SELECT id FROM config WHERE theme_id = 1 LIMIT 1");
if (!$res || $res->num_rows == 0) {
    $conn->query("INSERT IGNORE INTO config (theme_id) VALUES (1)");
}

$conn->query("CREATE TABLE IF NOT EXISTS `propaganda` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `image_url` varchar(255) NOT NULL,
  `item_order` int DEFAULT 0,
  `active` tinyint DEFAULT 1
)");
$conn->query("ALTER TABLE propaganda ADD COLUMN theme_id INTEGER DEFAULT 1");

$conn->query("CREATE TABLE IF NOT EXISTS `vertical_buttons` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `button_index` INTEGER NOT NULL,
  `label` varchar(50) DEFAULT '',
  `icon_url` varchar(255) DEFAULT '',
  `package_name` varchar(255) DEFAULT '',
  `action_type` varchar(20) DEFAULT 'app',
  `active` tinyint DEFAULT 1,
  UNIQUE(`theme_id`, `button_index`)
)");

for ($i = 1; $i <= 3; $i++) {
    $conn->query("INSERT IGNORE INTO vertical_buttons (theme_id, button_index, label) VALUES (1, $i, 'BOTÃO $i')");
}

$conn->query("CREATE TABLE IF NOT EXISTS `app_store` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `name` varchar(100) NOT NULL,
  `package_name` varchar(255) NOT NULL,
  `icon_url` varchar(255) DEFAULT '',
  `apk_url` varchar(255) DEFAULT '',
  `description` text,
  `version` varchar(20) DEFAULT '1.0.0',
  `item_order` int DEFAULT 0,
  UNIQUE(`theme_id`, `package_name`)
)");

$conn->query("CREATE TABLE IF NOT EXISTS `launcher_apps` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `app_name` varchar(100) DEFAULT '',
  `package_name` varchar(255) NOT NULL,
  `icon_url` varchar(255) DEFAULT '',
  `apk_url` varchar(255) DEFAULT '',
  `item_order` int DEFAULT 0,
  UNIQUE(`theme_id`, `package_name`)
)");

// Migração segura das constraints UNIQUE
$tables_to_migrate = [
    'vertical_buttons' => "CREATE TABLE `vertical_buttons` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `button_index` INTEGER NOT NULL,
  `label` varchar(50) DEFAULT '',
  `icon_url` varchar(255) DEFAULT '',
  `package_name` varchar(255) DEFAULT '',
  `action_type` varchar(20) DEFAULT 'app',
  `active` tinyint DEFAULT 1,
  UNIQUE(`theme_id`, `button_index`)
)",
    'app_store' => "CREATE TABLE `app_store` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `name` varchar(100) NOT NULL,
  `package_name` varchar(255) NOT NULL,
  `icon_url` varchar(255) DEFAULT '',
  `apk_url` varchar(255) DEFAULT '',
  `description` text,
  `version` varchar(20) DEFAULT '1.0.0',
  `item_order` int DEFAULT 0,
  UNIQUE(`theme_id`, `package_name`)
)",
    'launcher_apps' => "CREATE TABLE `launcher_apps` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `theme_id` INTEGER DEFAULT 1,
  `app_name` varchar(100) DEFAULT '',
  `package_name` varchar(255) NOT NULL,
  `icon_url` varchar(255) DEFAULT '',
  `apk_url` varchar(255) DEFAULT '',
  `item_order` int DEFAULT 0,
  UNIQUE(`theme_id`, `package_name`)
)"
];

foreach ($tables_to_migrate as $table => $create_sql) {
    $res = $conn->query("SELECT sql FROM sqlite_master WHERE type='table' AND name='$table'");
    if ($res && $row = $res->fetch_assoc()) {
        $sql = $row['sql'];
        if (strpos($sql, 'UNIQUE(`theme_id`') === false && strpos($sql, 'UNIQUE("theme_id"') === false) {
            $conn->query("ALTER TABLE `$table` RENAME TO `${table}_old`");
            $conn->query($create_sql);
            
            $colsRes = $conn->query("PRAGMA table_info(`${table}_old`)");
            $old_cols = [];
            $has_theme_old = false;
            while ($c = $colsRes->fetch_assoc()) {
                $old_cols[] = "`" . $c['name'] . "`";
                if ($c['name'] == 'theme_id') $has_theme_old = true;
            }
            $cols_str = implode(", ", $old_cols);
            
            if ($has_theme_old) {
                $conn->query("INSERT OR IGNORE INTO `$table` ($cols_str) SELECT $cols_str FROM `${table}_old`");
            } else {
                $conn->query("INSERT OR IGNORE INTO `$table` ($cols_str, `theme_id`) SELECT $cols_str, 1 FROM `${table}_old`");
            }
            $conn->query("DROP TABLE `${table}_old`");
        }
    }
}

// Tabela de Dispositivos (Bloqueio/Kill Switch)
$conn->query("CREATE TABLE IF NOT EXISTS `devices` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `device_id` varchar(255) UNIQUE NOT NULL,
  `theme_id` INTEGER DEFAULT 1,
  `message_title` varchar(255) DEFAULT NULL,
  `message_body` text DEFAULT NULL,
  `expiration_date` datetime DEFAULT NULL,
  `active` tinyint DEFAULT 1,
  `note` varchar(255) DEFAULT '',
  `autoplay_package` varchar(255) DEFAULT '',
  `last_check` datetime DEFAULT CURRENT_TIMESTAMP
)");
$conn->query("ALTER TABLE devices ADD COLUMN theme_id INTEGER DEFAULT 1");
$conn->query("ALTER TABLE devices ADD COLUMN message_title varchar(255) DEFAULT NULL");
$conn->query("ALTER TABLE devices ADD COLUMN message_body text DEFAULT NULL");
$conn->query("ALTER TABLE devices ADD COLUMN autoplay_package varchar(255) DEFAULT ''");

// Tabela de Planos
$conn->query("CREATE TABLE IF NOT EXISTS `plans` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` varchar(100) NOT NULL,
  `duration_days` int NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `active` tinyint DEFAULT 1
)");

// Inicializar Planos Padrão
$res = $conn->query("SELECT id FROM plans LIMIT 1");
if (!$res || $res->num_rows == 0) {
    $conn->query("INSERT INTO plans (name, duration_days, price) VALUES ('Mensal', 30, 29.90)");
    $conn->query("INSERT INTO plans (name, duration_days, price) VALUES ('Trimestral', 90, 79.90)");
    $conn->query("INSERT INTO plans (name, duration_days, price) VALUES ('Semestral', 180, 149.90)");
    $conn->query("INSERT INTO plans (name, duration_days, price) VALUES ('Anual', 365, 269.90)");
}

// Tabela de Pagamentos (PIX)
$conn->query("CREATE TABLE IF NOT EXISTS `payments` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `device_id` varchar(255) NOT NULL,
  `plan_id` int NOT NULL,
  `external_reference` varchar(255) UNIQUE,
  `payment_id` varchar(100) DEFAULT NULL,
  `status` varchar(20) DEFAULT 'pending',
  `qr_code` text,
  `qr_code_base64` text,
  `amount` decimal(10,2),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP
)");
?>