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

class Novajyin_DB
{
    const DB_VERSION = '0.1.0';
    const OPTION_DB_VERSION = 'novajyin_db_version';

    public static function table($name)
    {
        global $wpdb;

        $tables = array(
            'inquiries' => $wpdb->prefix . 'nova_jyo_inquiries',
            'groups' => $wpdb->prefix . 'nova_jyo_inquiry_groups',
            'group_users' => $wpdb->prefix . 'nova_jyo_inquiry_group_users',
            'statuses' => $wpdb->prefix . 'nova_jyo_inquiry_statuses',
            'priorities' => $wpdb->prefix . 'nova_jyo_inquiry_priorities',
            'histories' => $wpdb->prefix . 'nova_jyo_inquiry_histories',
            'memos' => $wpdb->prefix . 'nova_jyo_inquiry_memos',
            'rules' => $wpdb->prefix . 'nova_jyo_inquiry_rules',
        );

        return isset($tables[$name]) ? $tables[$name] : '';
    }

    public static function install()
    {
        global $wpdb;

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

        $charset_collate = $wpdb->get_charset_collate();
        $inquiries = self::table('inquiries');
        $groups = self::table('groups');
        $group_users = self::table('group_users');
        $statuses = self::table('statuses');
        $priorities = self::table('priorities');
        $histories = self::table('histories');
        $memos = self::table('memos');
        $rules = self::table('rules');

        $sql = array();

        $sql[] = "CREATE TABLE {$inquiries} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            form_id bigint(20) unsigned NOT NULL DEFAULT 0,
            form_name varchar(255) NOT NULL DEFAULT '',
            submitted_at datetime NOT NULL,
            name varchar(255) NOT NULL DEFAULT '',
            email varchar(255) NOT NULL DEFAULT '',
            tel varchar(100) NOT NULL DEFAULT '',
            subject varchar(255) NOT NULL DEFAULT '',
            inquiry_type varchar(255) NOT NULL DEFAULT '',
            shop_name varchar(255) NOT NULL DEFAULT '',
            message longtext NULL,
            raw_data_json longtext NULL,
            attachments_json longtext NULL,
            status_id bigint(20) unsigned NULL,
            assignee_user_id bigint(20) unsigned NULL,
            assignee_group_id bigint(20) unsigned NULL,
            priority_id bigint(20) unsigned NULL,
            due_date datetime NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY form_id (form_id),
            KEY status_id (status_id),
            KEY assignee_user_id (assignee_user_id),
            KEY assignee_group_id (assignee_group_id),
            KEY priority_id (priority_id),
            KEY submitted_at (submitted_at),
            KEY due_date (due_date)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$groups} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            group_name varchar(255) NOT NULL DEFAULT '',
            description text NULL,
            notification_email varchar(255) NOT NULL DEFAULT '',
            sort_order int(11) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY is_active (is_active),
            KEY sort_order (sort_order)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$group_users} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            group_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY group_user (group_id,user_id),
            KEY group_id (group_id),
            KEY user_id (user_id)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$statuses} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            status_name varchar(255) NOT NULL DEFAULT '',
            sort_order int(11) NOT NULL DEFAULT 0,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            is_closed tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY sort_order (sort_order),
            KEY is_default (is_default),
            KEY is_closed (is_closed)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$priorities} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            priority_name varchar(255) NOT NULL DEFAULT '',
            sort_order int(11) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY sort_order (sort_order)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$histories} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            inquiry_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            history_type varchar(50) NOT NULL DEFAULT 'manual_note',
            content longtext NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY inquiry_id (inquiry_id),
            KEY user_id (user_id),
            KEY history_type (history_type)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$memos} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            inquiry_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            memo_content longtext NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY inquiry_id (inquiry_id),
            KEY user_id (user_id)
        ) {$charset_collate};";

        $sql[] = "CREATE TABLE {$rules} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            rule_name varchar(255) NOT NULL DEFAULT '',
            conditions_json longtext NULL,
            actions_json longtext NULL,
            sort_order int(11) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY sort_order (sort_order),
            KEY is_active (is_active)
        ) {$charset_collate};";

        foreach ($sql as $statement) {
            dbDelta($statement);
        }

        self::seed_defaults();
        update_option(self::OPTION_DB_VERSION, self::DB_VERSION, false);
    }

    public static function seed_defaults()
    {
        global $wpdb;

        $statuses = self::table('statuses');
        $priorities = self::table('priorities');
        $now = current_time('mysql');

        $status_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$statuses}");
        if ($status_count === 0) {
            $default_statuses = array(
                array('未対応', 10, 1, 0),
                array('対応中', 20, 0, 0),
                array('確認待ち', 30, 0, 0),
                array('対応済み', 40, 0, 1),
                array('保留', 50, 0, 0),
                array('不要', 60, 0, 1),
            );

            foreach ($default_statuses as $status) {
                $wpdb->insert(
                    $statuses,
                    array(
                        'status_name' => $status[0],
                        'sort_order' => $status[1],
                        'is_default' => $status[2],
                        'is_closed' => $status[3],
                        'created_at' => $now,
                        'updated_at' => $now,
                    ),
                    array('%s', '%d', '%d', '%d', '%s', '%s')
                );
            }
        }

        $priority_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$priorities}");
        if ($priority_count === 0) {
            $default_priorities = array(
                array('低', 10),
                array('中', 20),
                array('高', 30),
                array('緊急', 40),
            );

            foreach ($default_priorities as $priority) {
                $wpdb->insert(
                    $priorities,
                    array(
                        'priority_name' => $priority[0],
                        'sort_order' => $priority[1],
                        'created_at' => $now,
                        'updated_at' => $now,
                    ),
                    array('%s', '%d', '%s', '%s')
                );
            }
        }
    }
}





