<?php

if (!defined('ABSPATH')) { exit; }

function aieo_dmm_fulfillment_install_schema() {
    aieo_dmm_create_fulfillment_vouchers_table();
    aieo_dmm_create_fulfillment_checkpoints_table();
    aieo_dmm_create_fulfillment_documents_table();
    aieo_dmm_create_fulfillment_sequences_table();
    aieo_dmm_create_fulfillment_settings_table();
    aieo_dmm_create_fulfillment_closings_table();
}

function aieo_dmm_create_fulfillment_vouchers_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_vouchers';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                    bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id`              bigint unsigned NOT NULL,
        `carrier`               varchar(80)     NOT NULL,
        `carrier_account`       varchar(40)     NOT NULL DEFAULT '',
        `voucher_no`            varchar(40)     NOT NULL DEFAULT '',
        `parent_voucher_no`     varchar(40)     NOT NULL DEFAULT '',
        `job_id`                bigint unsigned DEFAULT NULL,
        `pieces`                smallint unsigned NOT NULL DEFAULT 1,
        `weight`                decimal(8,3)    DEFAULT NULL,
        `cod_amount`            decimal(10,2)   DEFAULT NULL,
        `ins_amount`            decimal(10,2)   DEFAULT NULL,
        `carrier_cost`          decimal(10,2)   DEFAULT NULL,
        `carrier_vat`           decimal(10,2)   DEFAULT NULL,
        `cost_source`           varchar(16)     NOT NULL DEFAULT '',
        `customer_shipping_paid` decimal(10,2)  DEFAULT NULL,
        `services_csv`          varchar(120)    NOT NULL DEFAULT '',
        `content_type_id`       smallint unsigned DEFAULT NULL,
        `content_description`   varchar(120)    NOT NULL DEFAULT '',
        `reference_1`           varchar(80)     NOT NULL DEFAULT '',
        `reference_2`           varchar(80)     NOT NULL DEFAULT '',
        `status`                varchar(24)     NOT NULL DEFAULT 'created',
        `status_code`           varchar(24)     NOT NULL DEFAULT '',
        `status_at`             datetime        DEFAULT NULL,
        `consignee`             varchar(120)    NOT NULL DEFAULT '',
        `arrived_at`            datetime        DEFAULT NULL,
        `arrived_status`        varchar(24)     NOT NULL DEFAULT '',
        `delivered_at`          datetime        DEFAULT NULL,
        `label_pdf_path`        varchar(255)    NOT NULL DEFAULT '',
        `last_polled_at`        datetime        DEFAULT NULL,
        `closed_at`             datetime        DEFAULT NULL,
        `canceled_at`           datetime        DEFAULT NULL,
        `printed_at`            datetime        DEFAULT NULL,
        `print_count`           int unsigned    NOT NULL DEFAULT 0,
        `created_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `carrier_voucher_no` (`carrier`, `voucher_no`),
        KEY `order_id` (`order_id`),
        KEY `status_polled` (`status`, `last_polled_at`),
        KEY `carrier_status` (`carrier`, `status`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    
    
    
    if (function_exists('aieo_dmm_ensure_column')) {
        aieo_dmm_ensure_column($table, 'printed_at',  'datetime DEFAULT NULL');
        aieo_dmm_ensure_column($table, 'print_count', 'int unsigned NOT NULL DEFAULT 0');
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'postal_code', 'varchar(20) DEFAULT NULL');
    }

    
    
    
    
    
    
    aieo_dmm_fulfillment_widen_varchar_column($table, 'carrier', 80);
}

function aieo_dmm_create_fulfillment_checkpoints_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_checkpoints';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                bigint unsigned NOT NULL AUTO_INCREMENT,
        `voucher_id`        bigint unsigned NOT NULL,
        `code`              varchar(24)     NOT NULL DEFAULT '',
        `description`       text,
        `location`          varchar(120)    NOT NULL DEFAULT '',
        `checkpoint_at`     datetime        DEFAULT NULL,
        `recorded_at`       datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `voucher_code_at` (`voucher_id`, `code`, `checkpoint_at`),
        KEY `voucher_at` (`voucher_id`, `checkpoint_at`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_fulfillment_documents_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_documents';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                    bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id`              bigint unsigned NOT NULL,
        `type`                  varchar(20)     NOT NULL,
        `number`                varchar(40)     NOT NULL DEFAULT '',
        `number_seq`            int unsigned    DEFAULT NULL,
        `sequence_year`         smallint unsigned DEFAULT NULL,
        `issued_at`             datetime        DEFAULT NULL,
        `due_at`                datetime        DEFAULT NULL,
        `language`              varchar(5)      NOT NULL DEFAULT 'el',
        `template_id`           varchar(60)     NOT NULL DEFAULT '',
        `pdf_path`              varchar(255)    NOT NULL DEFAULT '',
        `pdf_hash`              char(64)        NOT NULL DEFAULT '',
        `attached_to_emails`    varchar(255)    NOT NULL DEFAULT '',
        `printed_at`            datetime        DEFAULT NULL,
        `print_count`           int unsigned    NOT NULL DEFAULT 0,
        `created_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at`            datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `type_number` (`type`, `number`),
        KEY `order_type` (`order_id`, `type`),
        KEY `issued_at` (`issued_at`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    
    
    
    
    
    if (function_exists('aieo_dmm_ensure_column')) {
        aieo_dmm_ensure_column($table, 'printed_at',  'datetime DEFAULT NULL');
        aieo_dmm_ensure_column($table, 'print_count', 'int unsigned NOT NULL DEFAULT 0');
    }
}

function aieo_dmm_create_fulfillment_sequences_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_sequences';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `type`              varchar(20)     NOT NULL,
        `year`              smallint unsigned NOT NULL,
        `next_value`        int unsigned    NOT NULL DEFAULT 1,
        `updated_at`        datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`type`, `year`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_fulfillment_settings_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_settings';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `setting_key`       varchar(80)     NOT NULL,
        `setting_value`     longtext,
        `updated_at`        datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`setting_key`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_fulfillment_closings_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_fulfillment_closings';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id`                bigint unsigned NOT NULL AUTO_INCREMENT,
        `carrier`           varchar(80)     NOT NULL,
        `closing_mode`      varchar(16)     NOT NULL DEFAULT 'now',
        `from_date`         date            DEFAULT NULL,
        `to_date`           date            DEFAULT NULL,
        `voucher_count`     int unsigned    NOT NULL DEFAULT 0,
        `voucher_ids`       longtext,
        `manifest_rows`     longtext,
        `status`            varchar(16)     NOT NULL DEFAULT 'ok',
        `error_message`     varchar(512)    NOT NULL DEFAULT '',
        `closed_by`         bigint unsigned DEFAULT NULL,
        `pdf_path`          varchar(255)    NOT NULL DEFAULT '',
        `closed_at`         datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `created_at`        datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `carrier_closed_at` (`carrier`, `closed_at`),
        KEY `status` (`status`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    aieo_dmm_fulfillment_widen_varchar_column($table, 'carrier', 80);
}

function aieo_dmm_fulfillment_widen_varchar_column($table, $column, $size) {
    global $wpdb;
    $size = (int) $size;
    if ($size <= 0) { return; }
    $current = $wpdb->get_var($wpdb->prepare(
        "SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
        $table,
        $column
    ));
    if ($current === null) { return; } 
    if ((int) $current >= $size) { return; } 
    
    $wpdb->query("ALTER TABLE `{$table}` MODIFY COLUMN `{$column}` varchar({$size}) NOT NULL");
}