/*
/**
 * connection.php
 *
 * Centraliza:
 *  - Conexão PDO MySQL
 *  - Nomes das tabelas via classe Table
 *  - Criação automática das tabelas
 *
 * Incluir em todos os ficheiros:
 *      require_once __DIR__ . '/connection.php';
 */
/*
class Table {
    public const INFLUENCERS           = 'influencers';
    public const CUSTOMERS             = 'customers';
    public const PLANS                 = 'plans';
    public const BILLING_CYCLES        = 'billing_cycles';
    public const SUBSCRIPTIONS         = 'subscriptions';
    public const SUBSCRIPTION_PAYMENTS = 'subscription_payments';
    public const PAYOUTS               = 'payouts';
    public const POSWARE_DOWNLOADS = 'posware_downloads';
}

class DB {

    private static ?PDO $pdo = null;

    public static function connect(): PDO
    {
        if (self::$pdo === null) {
*/
            /* ---------------------------------------------------------
             *  DATABASE CREDENTIALS
             * --------------------------------------------------------- */
            //local CREDENTIALS
            /*$host = "localhost";
            $user = "root";
            $pass = "";
            $dbname = "services";
            $charset = "utf8mb4";*/

            /*//production CREDENTIALS
            $host = "169.239.182.110";
            $user = "dnynkikr_ndx_pw";
            $pass = "mysPASSWORD97#";
            $dbname = "dnynkikr_services";
            $charset = "utf8mb4";

            $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

            /* ---------------------------------------------------------
             *  CONNECT PDO
             * --------------------------------------------------------- */
            self::$pdo = new PDO($dsn, $user, $pass, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);

            /* Ativar suporte unicode corretamente */
            self::$pdo->exec("SET NAMES utf8mb4");
            self::$pdo->exec("SET CHARACTER SET utf8mb4");

            /* Criar tabelas */
            self::createTables();
        }

        return self::$pdo;
    }

    private static function createTables(): void
    {
        $sql = [

            // Influencers
            "CREATE TABLE IF NOT EXISTS " . Table::INFLUENCERS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255),
                cel VARCHAR(15),
                address VARCHAR(255),
                country VARCHAR(20),
                province_or_state VARCHAR(20),
                promo_code VARCHAR(190) UNIQUE NOT NULL,
                commission_rate DECIMAL(10,2) DEFAULT 10,
                agreement_start DATE,
                agreement_end DATE,
                status VARCHAR(50) DEFAULT 'active',
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Customers
            "CREATE TABLE IF NOT EXISTS " . Table::CUSTOMERS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255),
                influencer_id INT,
                FOREIGN KEY (influencer_id)
                    REFERENCES " . Table::INFLUENCERS . "(id)
                    ON DELETE SET NULL
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Plans
            "CREATE TABLE IF NOT EXISTS " . Table::PLANS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                plan_name VARCHAR(255) NOT NULL,
                base_price DECIMAL(10,2) NOT NULL,
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Billing cycles
            "CREATE TABLE IF NOT EXISTS " . Table::BILLING_CYCLES . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                cycle_name VARCHAR(255) NOT NULL,
                cycle_multiplier INT NOT NULL,
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Subscriptions
            "CREATE TABLE IF NOT EXISTS " . Table::SUBSCRIPTIONS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                customer_id INT NOT NULL,
                plan_id INT NOT NULL,
                cycle_id INT NOT NULL,
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                status VARCHAR(50) DEFAULT 'active',
                auto_renew TINYINT(1) DEFAULT 1,
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                FOREIGN KEY (customer_id) REFERENCES " . Table::CUSTOMERS . "(id) ON DELETE CASCADE,
                FOREIGN KEY (plan_id) REFERENCES " . Table::PLANS . "(id),
                FOREIGN KEY (cycle_id) REFERENCES " . Table::BILLING_CYCLES . "(id)
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Subscription payments
            "CREATE TABLE IF NOT EXISTS " . Table::SUBSCRIPTION_PAYMENTS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                subscription_id INT NOT NULL,
                influencer_id INT,
                payment_amount DECIMAL(10,2) NOT NULL,
                payment_date DATE NOT NULL,
                commission_amount DECIMAL(10,2) DEFAULT 0,
                commission_status VARCHAR(50) DEFAULT 'pending',
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                FOREIGN KEY (subscription_id) REFERENCES " . Table::SUBSCRIPTIONS . "(id) ON DELETE CASCADE,
                FOREIGN KEY (influencer_id)   REFERENCES " . Table::INFLUENCERS . "(id)
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Payouts
            "CREATE TABLE IF NOT EXISTS " . Table::PAYOUTS . " (
                id INT AUTO_INCREMENT PRIMARY KEY,
                influencer_id INT NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                payout_date DATE NOT NULL,
                status VARCHAR(50) DEFAULT 'paid',
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                FOREIGN KEY (influencer_id) REFERENCES " . Table::INFLUENCERS . "(id)
            ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",

            // Influencer Download Tracking
            "CREATE TABLE IF NOT EXISTS " . Table::POSWARE_DOWNLOADS . " (
    id INT AUTO_INCREMENT PRIMARY KEY,
    promo_code VARCHAR(190) UNIQUE,
    download_count INT DEFAULT 0,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_download DATETIME
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        ];

        $pdo = self::$pdo;

        foreach ($sql as $statement) {
            $pdo->exec($statement);
        }
    }
}*/

/* Auto-inicializar quando incluído */
/*DB::connect();
?>**
<br />
<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dnynkikr_services.posware_downloads' doesn't exist in /home/dnynkikr/public_html/php/pw_download.php:27
Stack trace:
#0 /home/dnynkikr/public_html/php/pw_download.php(27): PDO-&gt;prepare()
#1 {main}
  thrown in <b>/home/dnynkikr/public_html/php/pw_download.php</b> on line <b>27</b><br />