<?php
/**
 * Database Query Helper Class
 * Wraps WordPress $wpdb operations for Proto Preview
 */

class Proto_Preview_DB {

    protected $wpdb;
    public $table_projects;
    public $table_pages;
    public $table_layouts;
    public $table_tour_steps;
    public $table_comments;
    public $table_comment_replies;
    public $table_section_approvals;

    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_projects          = $wpdb->prefix . 'pp_projects';
        $this->table_pages             = $wpdb->prefix . 'pp_pages';
        $this->table_layouts           = $wpdb->prefix . 'pp_layouts';
        $this->table_tour_steps        = $wpdb->prefix . 'pp_tour_steps';
        $this->table_comments          = $wpdb->prefix . 'pp_comments';
        $this->table_comment_replies   = $wpdb->prefix . 'pp_comment_replies';
        $this->table_section_approvals = $wpdb->prefix . 'pp_section_approvals';
    }

    public function get_projects($project_id = 0) {
        if ($project_id > 0) {
            $project = $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM {$this->table_projects} WHERE id = %d", $project_id), ARRAY_A);
            if (!$project) return null;
            return $this->hydrate_project($project);
        }

        $projects = $this->wpdb->get_results("SELECT * FROM {$this->table_projects} ORDER BY id ASC", ARRAY_A);
        foreach ($projects as &$p) {
            $p = $this->hydrate_project($p);
        }
        return $projects;
    }

    public function hydrate_project($project) {
        $pId = (int)$project['id'];

        // 1. Fetch Pages / Screens
        $pages = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM {$this->table_pages} WHERE project_id = %d ORDER BY page_order ASC, id ASC",
            $pId
        ), ARRAY_A);

        if (empty($pages)) {
            $this->wpdb->insert($this->table_pages, [
                'project_id' => $pId,
                'name' => 'Homepage',
                'slug' => 'home',
                'section_category' => 'Core Journey',
                'page_order' => 1
            ]);
            $newPageId = $this->wpdb->insert_id;
            $pages = [['id' => $newPageId, 'project_id' => $pId, 'name' => 'Homepage', 'slug' => 'home', 'section_category' => 'Core Journey', 'page_order' => 1]];
        }

        // 2. Fetch all layouts
        $layouts = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM {$this->table_layouts} WHERE project_id = %d ORDER BY id ASC",
            $pId
        ), ARRAY_A);

        foreach ($layouts as &$layout) {
            $lId = (int)$layout['id'];
            $steps = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT * FROM {$this->table_tour_steps} WHERE layout_id = %d ORDER BY step_order ASC, id ASC",
                $lId
            ), ARRAY_A);
            $layout['tour_steps'] = $steps;

            $comms_count = $this->wpdb->get_var($this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_comments} WHERE layout_id = %d",
                $lId
            ));
            $layout['comments_count'] = (int)$comms_count;
        }

        // 3. Nest layouts under their respective page
        foreach ($pages as &$page) {
            $pageLayouts = array_values(array_filter($layouts, function($l) use ($page) {
                return (int)($l['page_id'] ?? 0) === (int)$page['id'];
            }));
            $page['layouts'] = $pageLayouts;
        }

        $project['pages'] = $pages;
        $project['layouts'] = $layouts;
        return $project;
    }

    public function create_project($name, $client_name, $description = '', $user_id = 1) {
        $token = bin2hex(random_bytes(16));
        $this->wpdb->insert($this->table_projects, [
            'name' => $name,
            'client_name' => $client_name ?: 'Client Stakeholder',
            'description' => $description,
            'share_token' => $token,
            'status' => 'active',
            'created_by' => $user_id,
            'created_at' => current_time('mysql')
        ]);
        $project_id = $this->wpdb->insert_id;

        // Auto-create default Homepage
        $this->wpdb->insert($this->table_pages, [
            'project_id' => $project_id,
            'name' => 'Homepage',
            'slug' => 'home',
            'section_category' => 'Core Journey',
            'page_order' => 1,
            'created_at' => current_time('mysql')
        ]);
        $page_id = $this->wpdb->insert_id;

        // Auto-create default layout variation
        $default_template = PROTO_PREVIEW_PLUGIN_URL . 'layouts/saas-v1.html';
        $this->wpdb->insert($this->table_layouts, [
            'project_id' => $project_id,
            'page_id' => $page_id,
            'version' => 'Option 1',
            'name' => $name . ' Option 1',
            'file_path' => $default_template,
            'badge' => 'Initial Draft',
            'color' => 'red',
            'description' => 'Initial prototype variation.',
            'is_selected_choice' => 1,
            'approval_status' => 'in_review',
            'created_at' => current_time('mysql')
        ]);
        $layout_id = $this->wpdb->insert_id;

        // Seed default tour steps
        $this->seed_default_steps($layout_id);

        return $this->get_projects($project_id);
    }

    public function delete_project($project_id) {
        // Cascade delete pages, layouts, steps, comments, approvals
        $pages = $this->wpdb->get_col($this->wpdb->prepare("SELECT id FROM {$this->table_pages} WHERE project_id = %d", $project_id));
        $layouts = $this->wpdb->get_col($this->wpdb->prepare("SELECT id FROM {$this->table_layouts} WHERE project_id = %d", $project_id));

        foreach ($layouts as $lId) {
            $this->wpdb->delete($this->table_tour_steps, ['layout_id' => $lId]);
            $this->wpdb->delete($this->table_comments, ['layout_id' => $lId]);
            $this->wpdb->delete($this->table_section_approvals, ['layout_id' => $lId]);
        }

        $this->wpdb->delete($this->table_layouts, ['project_id' => $project_id]);
        $this->wpdb->delete($this->table_pages, ['project_id' => $project_id]);
        $this->wpdb->delete($this->table_projects, ['id' => $project_id]);
        return true;
    }

    public function create_page($project_id, $name, $slug = '') {
        if (empty($slug)) {
            $slug = sanitize_title($name);
        }
        $this->wpdb->insert($this->table_pages, [
            'project_id' => $project_id,
            'name' => $name,
            'slug' => $slug,
            'section_category' => 'Core Journey',
            'page_order' => 1,
            'created_at' => current_time('mysql')
        ]);
        $page_id = $this->wpdb->insert_id;

        // Create default layout variation for this page
        $default_template = PROTO_PREVIEW_PLUGIN_URL . 'layouts/saas-v1.html';
        $this->wpdb->insert($this->table_layouts, [
            'project_id' => $project_id,
            'page_id' => $page_id,
            'version' => 'Option 1',
            'name' => $name . ' Option 1',
            'file_path' => $default_template,
            'badge' => 'Initial Draft',
            'color' => 'red',
            'description' => 'Initial variation.',
            'is_selected_choice' => 1,
            'approval_status' => 'in_review',
            'created_at' => current_time('mysql')
        ]);
        $layout_id = $this->wpdb->insert_id;
        $this->seed_default_steps($layout_id);

        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM {$this->table_pages} WHERE id = %d", $page_id), ARRAY_A);
    }

    public function delete_page($page_id) {
        $layouts = $this->wpdb->get_col($this->wpdb->prepare("SELECT id FROM {$this->table_layouts} WHERE page_id = %d", $page_id));
        foreach ($layouts as $lId) {
            $this->wpdb->delete($this->table_tour_steps, ['layout_id' => $lId]);
            $this->wpdb->delete($this->table_comments, ['layout_id' => $lId]);
            $this->wpdb->delete($this->table_section_approvals, ['layout_id' => $lId]);
        }
        $this->wpdb->delete($this->table_layouts, ['page_id' => $page_id]);
        $this->wpdb->delete($this->table_pages, ['id' => $page_id]);
        return true;
    }

    public function create_layout($project_id, $page_id, $name, $file_path, $badge = 'New Option', $color = 'red') {
        $count = $this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(*) FROM {$this->table_layouts} WHERE page_id = %d", $page_id));
        $version = 'Option ' . ((int)$count + 1);

        $this->wpdb->insert($this->table_layouts, [
            'project_id' => $project_id,
            'page_id' => $page_id,
            'version' => $version,
            'name' => $name,
            'file_path' => $file_path,
            'badge' => $badge,
            'color' => $color,
            'description' => 'Prototype variation.',
            'is_selected_choice' => 0,
            'approval_status' => 'in_review',
            'created_at' => current_time('mysql')
        ]);
        $layout_id = $this->wpdb->insert_id;
        $this->seed_default_steps($layout_id);

        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM {$this->table_layouts} WHERE id = %d", $layout_id), ARRAY_A);
    }

    public function choose_variation($layout_id, $user_id = 1) {
        $layout = $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM {$this->table_layouts} WHERE id = %d", $layout_id), ARRAY_A);
        if (!$layout) return false;

        $page_id = (int)$layout['page_id'];
        // Reset siblings on same page
        $this->wpdb->update($this->table_layouts, ['is_selected_choice' => 0], ['page_id' => $page_id]);

        // Mark as chosen
        $this->wpdb->update($this->table_layouts, [
            'is_selected_choice' => 1,
            'approval_status' => 'approved',
            'selected_by_user_id' => $user_id,
            'selected_at' => current_time('mysql')
        ], ['id' => $layout_id]);

        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM {$this->table_layouts} WHERE id = %d", $layout_id), ARRAY_A);
    }

    public function delete_layout($layout_id) {
        $this->wpdb->delete($this->table_tour_steps, ['layout_id' => $layout_id]);
        $this->wpdb->delete($this->table_comments, ['layout_id' => $layout_id]);
        $this->wpdb->delete($this->table_section_approvals, ['layout_id' => $layout_id]);
        $this->wpdb->delete($this->table_layouts, ['id' => $layout_id]);
        return true;
    }

    public function get_comments($layout_id = 0) {
        if ($layout_id > 0) {
            $sql = "SELECT c.*, u.display_name as author_name, u.user_email as author_email, l.name as layout_name, p.name as project_name
                    FROM {$this->table_comments} c
                    LEFT JOIN {$this->wpdb->users} u ON c.user_id = u.ID
                    LEFT JOIN {$this->table_layouts} l ON c.layout_id = l.id
                    LEFT JOIN {$this->table_projects} p ON l.project_id = p.id
                    WHERE c.layout_id = %d
                    ORDER BY c.id ASC";
            $comments = $this->wpdb->get_results($this->wpdb->prepare($sql, $layout_id), ARRAY_A);
        } else {
            $sql = "SELECT c.*, u.display_name as author_name, u.user_email as author_email, l.name as layout_name, p.name as project_name
                    FROM {$this->table_comments} c
                    LEFT JOIN {$this->wpdb->users} u ON c.user_id = u.ID
                    LEFT JOIN {$this->table_layouts} l ON c.layout_id = l.id
                    LEFT JOIN {$this->table_projects} p ON l.project_id = p.id
                    ORDER BY c.id DESC";
            $comments = $this->wpdb->get_results($sql, ARRAY_A);
        }

        foreach ($comments as &$c) {
            $replies = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT r.*, u.display_name as author_name, u.user_email as author_email
                 FROM {$this->table_comment_replies} r
                 LEFT JOIN {$this->wpdb->users} u ON r.user_id = u.ID
                 WHERE r.comment_id = %d ORDER BY r.id ASC",
                $c['id']
            ), ARRAY_A);

            $c['author'] = [
                'id'     => $c['user_id'],
                'name'   => $c['author_name'] ?: 'Client Stakeholder',
                'role'   => user_can($c['user_id'], 'manage_options') ? 'designer' : 'client',
                'avatar' => get_avatar_url($c['user_id'])
            ];
            $c['resolved'] = (bool)$c['is_resolved'];
            $c['createdAt'] = date('M j, g:i a', strtotime($c['created_at']));
            $c['replies'] = array_map(function($rep) {
                return [
                    'id'        => $rep['id'],
                    'text'      => $rep['text'],
                    'createdAt' => date('M j, g:i a', strtotime($rep['created_at'])),
                    'author'    => [
                        'id'     => $rep['user_id'],
                        'name'   => $rep['author_name'] ?: 'Team Member',
                        'role'   => user_can($rep['user_id'], 'manage_options') ? 'designer' : 'client',
                        'avatar' => get_avatar_url($rep['user_id'])
                    ]
                ];
            }, $replies);
        }

        return $comments;
    }

    public function create_comment($layout_id, $user_id, $x_percent, $y_percent, $text) {
        $this->wpdb->insert($this->table_comments, [
            'layout_id' => $layout_id,
            'user_id' => $user_id,
            'x_percent' => $x_percent,
            'y_percent' => $y_percent,
            'text' => $text,
            'is_resolved' => 0,
            'created_at' => current_time('mysql')
        ]);
        $new_id = $this->wpdb->insert_id;
        $comms = $this->get_comments($layout_id);
        return array_values(array_filter($comms, fn($c) => $c['id'] == $new_id))[0] ?? null;
    }

    public function toggle_resolve_comment($comment_id) {
        $curr = $this->wpdb->get_var($this->wpdb->prepare("SELECT is_resolved FROM {$this->table_comments} WHERE id = %d", $comment_id));
        $new_val = $curr ? 0 : 1;
        $this->wpdb->update($this->table_comments, ['is_resolved' => $new_val], ['id' => $comment_id]);
        return ['id' => $comment_id, 'is_resolved' => (bool)$new_val];
    }

    public function get_approvals($layout_id, $user_id = 1) {
        $total_steps = (int)$this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(*) FROM {$this->table_tour_steps} WHERE layout_id = %d", $layout_id));
        $approvals = $this->wpdb->get_results($this->wpdb->prepare("SELECT * FROM {$this->table_section_approvals} WHERE layout_id = %d AND user_id = %d ORDER BY step_order ASC", $layout_id, $user_id), ARRAY_A);

        $approved_count = 0;
        foreach ($approvals as $a) {
            if ($a['status'] === 'approved') $approved_count++;
        }

        return [
            'layout_id' => $layout_id,
            'total_steps' => $total_steps,
            'approved_count' => $approved_count,
            'is_fully_approved' => ($total_steps > 0 && $approved_count >= $total_steps),
            'approvals' => $approvals
        ];
    }

    public function save_approval($layout_id, $user_id, $step_order, $target_selector, $status, $note = '') {
        $this->wpdb->replace($this->table_section_approvals, [
            'layout_id' => $layout_id,
            'user_id' => $user_id,
            'step_order' => $step_order,
            'target_selector' => $target_selector,
            'status' => $status,
            'note' => $note,
            'created_at' => current_time('mysql')
        ]);
        return $this->get_approvals($layout_id, $user_id);
    }

    public function seed_default_steps($layout_id) {
        $defaultSteps = [
            [1, '#site-logo', '1. Brand Identity & Logo Lockup', 'Review the modernized dynamic brand emblem.', 'bottom'],
            [2, '#nav-menu', '2. Streamlined Navigation Menu', 'Review primary navigation structure.', 'bottom'],
            [3, '#header-cta', '3. Primary Conversion Trigger', 'Check conversion trigger button.', 'bottom'],
            [4, '#hero-section', '4. Hero Value Proposition', 'Review headline messaging clarity.', 'bottom'],
            [5, '#hero-cta', '5. Primary Hero Actions', 'Evaluate CTA buttons and trust badges.', 'top'],
            [6, '#features-grid', '6. Core Features & Capabilities', 'Review core value pillars and Bento grid layout.', 'top'],
            [7, '#site-footer', '7. Legal & Site Footer', 'Check footer navigation and compliance disclosures.', 'top']
        ];
        foreach ($defaultSteps as $step) {
            $this->wpdb->insert($this->table_tour_steps, [
                'layout_id' => $layout_id,
                'step_order' => $step[0],
                'target_selector' => $step[1],
                'title' => $step[2],
                'content' => $step[3],
                'placement' => $step[4],
                'created_at' => current_time('mysql')
            ]);
        }
    }
}