<?php
/**
 * Plugin Activator for Database Tables
 */

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

class Proto_Preview_Activator {

    public static function activate() {
        global $wpdb;
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        $charset_collate = $wpdb->get_charset_collate();

        // 1. Comments Table
        $table_comments = $wpdb->prefix . 'pp_comments';
        $sql_comments = "CREATE TABLE $table_comments (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            layout_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL DEFAULT 0,
            author_name varchar(150) NOT NULL DEFAULT '',
            author_email varchar(150) NOT NULL DEFAULT '',
            x_percent double NOT NULL,
            y_percent double NOT NULL,
            text text NOT NULL,
            is_resolved int(1) DEFAULT 0,
            is_locked int(1) DEFAULT 1,
            resolved_by bigint(20),
            resolved_at datetime,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY layout_id (layout_id)
        ) $charset_collate;";
        dbDelta($sql_comments);

        // 2. Comment Replies Table
        $table_replies = $wpdb->prefix . 'pp_comment_replies';
        $sql_replies = "CREATE TABLE $table_replies (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            comment_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL DEFAULT 0,
            author_name varchar(150) NOT NULL DEFAULT '',
            author_email varchar(150) NOT NULL DEFAULT '',
            text text NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY comment_id (comment_id)
        ) $charset_collate;";
        dbDelta($sql_replies);

        // 3. Story / Guide Steps Table (Design Walkthrough & Client Onboarding)
        $table_story_steps = $wpdb->prefix . 'pp_story_steps';
        $sql_story_steps = "CREATE TABLE $table_story_steps (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            project_id bigint(20) NOT NULL DEFAULT 0,
            page_id bigint(20) NOT NULL DEFAULT 0,
            layout_id bigint(20) NOT NULL,
            step_order int(11) NOT NULL DEFAULT 1,
            title varchar(255) NOT NULL,
            description text NOT NULL,
            category varchar(50) NOT NULL DEFAULT 'ux_rationale',
            target_type varchar(30) NOT NULL DEFAULT 'selector',
            target_selector varchar(255) NOT NULL DEFAULT '',
            rect_data text DEFAULT NULL,
            action_type varchar(50) NOT NULL DEFAULT 'none',
            action_target varchar(255) DEFAULT NULL,
            requires_approval int(1) NOT NULL DEFAULT 0,
            author_id bigint(20) NOT NULL DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY layout_step (layout_id, step_order)
        ) $charset_collate;";
        dbDelta($sql_story_steps);

        // 4. Story Approvals / Client Sign-off Table
        $table_story_approvals = $wpdb->prefix . 'pp_story_approvals';
        $sql_story_approvals = "CREATE TABLE $table_story_approvals (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            step_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL DEFAULT 0,
            client_name varchar(150) NOT NULL DEFAULT '',
            client_email varchar(150) NOT NULL DEFAULT '',
            status varchar(30) NOT NULL DEFAULT 'approved',
            comment text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY step_id (step_id)
        ) $charset_collate;";
        dbDelta($sql_story_approvals);
    }

    public static function upgrade_tables() {
        global $wpdb;
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        $charset_collate = $wpdb->get_charset_collate();

        $table_comments = $wpdb->prefix . 'pp_comments';
        $table_replies = $wpdb->prefix . 'pp_comment_replies';
        $table_story_steps = $wpdb->prefix . 'pp_story_steps';
        $table_story_approvals = $wpdb->prefix . 'pp_story_approvals';

        if ($wpdb->get_var("SHOW TABLES LIKE '$table_comments'") === $table_comments) {
            $col_name = $wpdb->get_var("SHOW COLUMNS FROM `$table_comments` LIKE 'author_name'");
            if (empty($col_name)) {
                $wpdb->query("ALTER TABLE `$table_comments` ADD COLUMN `author_name` varchar(150) NOT NULL DEFAULT '' AFTER `user_id`, ADD COLUMN `author_email` varchar(150) NOT NULL DEFAULT '' AFTER `author_name`");
            }
            $col_type = $wpdb->get_var("SHOW COLUMNS FROM `$table_comments` LIKE 'comment_type'");
            if (empty($col_type)) {
                $wpdb->query("ALTER TABLE `$table_comments` ADD COLUMN `comment_type` varchar(30) NOT NULL DEFAULT 'comment' AFTER `text`, ADD COLUMN `title` varchar(255) NOT NULL DEFAULT '' AFTER `y_percent`");
            }
        }

        if ($wpdb->get_var("SHOW TABLES LIKE '$table_replies'") === $table_replies) {
            $col_rep_name = $wpdb->get_var("SHOW COLUMNS FROM `$table_replies` LIKE 'author_name'");
            if (empty($col_rep_name)) {
                $wpdb->query("ALTER TABLE `$table_replies` ADD COLUMN `author_name` varchar(150) NOT NULL DEFAULT '' AFTER `user_id`, ADD COLUMN `author_email` varchar(150) NOT NULL DEFAULT '' AFTER `author_name`");
            }
        }

        if ($wpdb->get_var("SHOW TABLES LIKE '$table_story_steps'") !== $table_story_steps) {
            $sql_story_steps = "CREATE TABLE $table_story_steps (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                project_id bigint(20) NOT NULL DEFAULT 0,
                page_id bigint(20) NOT NULL DEFAULT 0,
                layout_id bigint(20) NOT NULL,
                step_order int(11) NOT NULL DEFAULT 1,
                title varchar(255) NOT NULL,
                description text NOT NULL,
                category varchar(50) NOT NULL DEFAULT 'ux_rationale',
                target_type varchar(30) NOT NULL DEFAULT 'selector',
                target_selector varchar(255) NOT NULL DEFAULT '',
                rect_data text DEFAULT NULL,
                action_type varchar(50) NOT NULL DEFAULT 'none',
                action_target varchar(255) DEFAULT NULL,
                requires_approval int(1) NOT NULL DEFAULT 0,
                author_id bigint(20) NOT NULL DEFAULT 0,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY layout_step (layout_id, step_order)
            ) $charset_collate;";
            dbDelta($sql_story_steps);
        }

        if ($wpdb->get_var("SHOW TABLES LIKE '$table_story_approvals'") !== $table_story_approvals) {
            $sql_story_approvals = "CREATE TABLE $table_story_approvals (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                step_id bigint(20) NOT NULL,
                user_id bigint(20) NOT NULL DEFAULT 0,
                client_name varchar(150) NOT NULL DEFAULT '',
                client_email varchar(150) NOT NULL DEFAULT '',
                status varchar(30) NOT NULL DEFAULT 'approved',
                comment text DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY step_id (step_id)
            ) $charset_collate;";
            dbDelta($sql_story_approvals);
        }
    }
}