<?php
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

function elementsell_create_database_tables() {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $charset_collate = $wpdb->get_charset_collate();

    $orders_table = $wpdb->prefix . 'elementsell_orders';
    $fields_table = $wpdb->prefix . 'elementsell_order_fields';

    $sql_orders = "CREATE TABLE $orders_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        order_uid VARCHAR(100) NOT NULL,
        payment_uid VARCHAR(100) DEFAULT '',
        payment_method VARCHAR(50) NOT NULL DEFAULT '',
        payment_status VARCHAR(50) NOT NULL DEFAULT 'pending',
        product_name VARCHAR(255) DEFAULT '',
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        currency VARCHAR(10) NOT NULL DEFAULT 'CZK',
        email VARCHAR(190) NOT NULL DEFAULT '',
        first_name VARCHAR(190) DEFAULT '',
        surname VARCHAR(190) DEFAULT '',
        phone VARCHAR(100) DEFAULT '',
        vs VARCHAR(50) DEFAULT '',
        pay_url TEXT NULL,
        return_url TEXT NULL,
        customer_id BIGINT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME DEFAULT NULL,
        paid_at DATETIME DEFAULT NULL,
        PRIMARY KEY  (id),
        KEY order_uid (order_uid),
        KEY payment_uid (payment_uid),
        KEY payment_status (payment_status),
        KEY payment_method (payment_method),
        KEY email (email)
    ) $charset_collate;";

    $sql_fields = "CREATE TABLE $fields_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        order_id BIGINT UNSIGNED NOT NULL,
        field_name VARCHAR(190) NOT NULL,
        field_value LONGTEXT NULL,
        created_at DATETIME NOT NULL,
        PRIMARY KEY  (id),
        KEY order_id (order_id),
        KEY field_name (field_name)
    ) $charset_collate;";

    dbDelta($sql_orders);
    dbDelta($sql_fields);

$email_log_table = $wpdb->prefix . 'elementsell_email_log';

$sql_email_log = "CREATE TABLE $email_log_table (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL DEFAULT '',
    subject TEXT NULL,
    body LONGTEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'sent',
    transport VARCHAR(20) NOT NULL DEFAULT '',
    from_email VARCHAR(190) NOT NULL DEFAULT '',
    error_message TEXT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY order_id (order_id),
    KEY email (email),
    KEY status (status)
) $charset_collate;";

dbDelta($sql_email_log);

    $invoices_table = $wpdb->prefix . 'elementsell_invoices';

    $sql_invoices = "CREATE TABLE $invoices_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        order_id BIGINT UNSIGNED NOT NULL,
        invoice_number VARCHAR(20) NOT NULL,
        type VARCHAR(20) NOT NULL DEFAULT 'invoice',
        status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
        issue_date DATE NOT NULL,
        due_date DATE DEFAULT NULL,
        paid_date DATE DEFAULT NULL,
        product_name VARCHAR(255) DEFAULT '',
        price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
        pdf_path TEXT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME DEFAULT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY invoice_number (invoice_number),
        KEY order_id (order_id),
        KEY status (status),
        KEY type (type)
    ) $charset_collate;";

    dbDelta($sql_invoices);

    $billing_invoices_table = $wpdb->prefix . 'elementsell_billing_invoices';

    $sql_billing_invoices = "CREATE TABLE $billing_invoices_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        invoice_number VARCHAR(32) NOT NULL,
        payment_uid VARCHAR(100) NOT NULL DEFAULT '',
        billing_year SMALLINT UNSIGNED NULL,
        billing_month TINYINT UNSIGNED NULL,
        invoice_kind VARCHAR(20) NOT NULL DEFAULT 'fee',
        description TEXT NOT NULL,
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        currency VARCHAR(10) NOT NULL DEFAULT 'CZK',
        paid_date DATE NOT NULL,
        issue_date DATE NOT NULL,
        pdf_path TEXT NULL,
        remote_synced TINYINT(1) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY invoice_number (invoice_number),
        KEY billing_period (billing_year, billing_month),
        KEY payment_uid (payment_uid),
        KEY invoice_kind (invoice_kind)
    ) $charset_collate;";

    dbDelta( $sql_billing_invoices );

    $sales_stats_table = $wpdb->prefix . 'elementsell_sales_stats';

    $sql_sales_stats = "CREATE TABLE $sales_stats_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        stat_year SMALLINT UNSIGNED NOT NULL,
        stat_month TINYINT UNSIGNED NOT NULL,
        product_name VARCHAR(255) NOT NULL DEFAULT '',
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        sales_count INT UNSIGNED NOT NULL DEFAULT 0,
        revenue_sum DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        PRIMARY KEY (id),
        UNIQUE KEY period_product_amount (stat_year, stat_month, product_name, amount),
        KEY period (stat_year, stat_month)
    ) $charset_collate;";

    dbDelta( $sql_sales_stats );

    $orders_archive_table = $wpdb->prefix . 'elementsell_orders_archive';

    $sql_orders_archive = "CREATE TABLE $orders_archive_table (
        id BIGINT UNSIGNED NOT NULL,
        order_uid VARCHAR(100) NOT NULL,
        payment_uid VARCHAR(100) DEFAULT '',
        payment_method VARCHAR(50) NOT NULL DEFAULT '',
        payment_status VARCHAR(50) NOT NULL DEFAULT 'pending',
        product_name VARCHAR(255) DEFAULT '',
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        currency VARCHAR(10) NOT NULL DEFAULT 'CZK',
        email VARCHAR(190) NOT NULL DEFAULT '',
        first_name VARCHAR(190) DEFAULT '',
        surname VARCHAR(190) DEFAULT '',
        phone VARCHAR(100) DEFAULT '',
        vs VARCHAR(50) DEFAULT '',
        pay_url TEXT NULL,
        return_url TEXT NULL,
        customer_id BIGINT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME DEFAULT NULL,
        paid_at DATETIME DEFAULT NULL,
        archived_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        KEY order_uid (order_uid),
        KEY payment_uid (payment_uid),
        KEY payment_status (payment_status),
        KEY email (email),
        KEY created_at (created_at)
    ) $charset_collate;";

    dbDelta( $sql_orders_archive );

    $fields_archive_table = $wpdb->prefix . 'elementsell_order_fields_archive';

    $sql_fields_archive = "CREATE TABLE $fields_archive_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        order_id BIGINT UNSIGNED NOT NULL,
        field_name VARCHAR(190) NOT NULL,
        field_value LONGTEXT NULL,
        created_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        KEY order_id (order_id),
        KEY field_name (field_name)
    ) $charset_collate;";

    dbDelta( $sql_fields_archive );

    $invoices_archive_table = $wpdb->prefix . 'elementsell_invoices_archive';

    $sql_invoices_archive = "CREATE TABLE $invoices_archive_table (
        id BIGINT UNSIGNED NOT NULL,
        order_id BIGINT UNSIGNED NOT NULL,
        invoice_number VARCHAR(20) NOT NULL,
        type VARCHAR(20) NOT NULL DEFAULT 'invoice',
        status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
        issue_date DATE NOT NULL,
        due_date DATE DEFAULT NULL,
        paid_date DATE DEFAULT NULL,
        product_name VARCHAR(255) DEFAULT '',
        price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
        pdf_path TEXT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME DEFAULT NULL,
        archived_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY invoice_number (invoice_number),
        KEY order_id (order_id),
        KEY status (status),
        KEY type (type)
    ) $charset_collate;";

    dbDelta( $sql_invoices_archive );

    $blacklist_table = $wpdb->prefix . 'elementsell_email_blacklist';

    $sql_blacklist = "CREATE TABLE $blacklist_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        email VARCHAR(190) NOT NULL,
        reason VARCHAR(20) NOT NULL DEFAULT 'manual',
        created_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY email (email),
        KEY reason (reason)
    ) $charset_collate;";

    dbDelta( $sql_blacklist );

    $landing_leads_table = $wpdb->prefix . 'elementsell_landing_leads';

    $sql_landing_leads = "CREATE TABLE $landing_leads_table (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        first_name VARCHAR(190) NOT NULL DEFAULT '',
        surname VARCHAR(190) NOT NULL DEFAULT '',
        website VARCHAR(255) NOT NULL DEFAULT '',
        product_intent TEXT NULL,
        created_at DATETIME NOT NULL,
        PRIMARY KEY (id),
        KEY created_at (created_at)
    ) $charset_collate;";

    dbDelta( $sql_landing_leads );
}

/**
 * Vytvoří chybějící tabulky bez nutnosti reaktivace pluginu.
 *
 * @return void
 */
function elementsell_maybe_upgrade_database_tables() {
    $installed = get_option( 'elementsell_db_version', '0' );

    if ( version_compare( (string) $installed, PF_VERSION, '<' ) ) {
        elementsell_create_database_tables();
        update_option( 'elementsell_db_version', PF_VERSION );
    }

    elementsell_ensure_email_log_table_columns();
    elementsell_ensure_mailing_list_tables();
    elementsell_ensure_landing_leads_table();

    if ( function_exists( 'elementsell_automation_maybe_upgrade_database' ) ) {
        elementsell_automation_maybe_upgrade_database();
    }
}

/**
 * Zajistí tabulku landing leadů.
 *
 * @return void
 */
function elementsell_ensure_landing_leads_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'elementsell_landing_leads';

    if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
        return;
    }

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    elementsell_create_database_tables();
}

/**
 * Zajistí tabulku blacklistu e-mailů pro mailing list.
 *
 * @return void
 */
function elementsell_ensure_mailing_list_tables() {
    global $wpdb;

    $table = $wpdb->prefix . 'elementsell_email_blacklist';

    if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
        return;
    }

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    elementsell_create_database_tables();
}

/**
 * Doplní chybějící sloupce v logu e-mailů (upgrade z verzí před 3.2.3).
 *
 * @return void
 */
function elementsell_ensure_email_log_table_columns() {
    global $wpdb;

    $table = $wpdb->prefix . 'elementsell_email_log';

    if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
        return;
    }

    $status_column = $wpdb->get_var(
        $wpdb->prepare(
            'SHOW COLUMNS FROM `' . $table . '` LIKE %s',
            'status'
        )
    );

    if ( $status_column === 'status' ) {
        return;
    }

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    elementsell_create_database_tables();
}