<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * Migration Controller
 * 
 * Handles migration from single-company to multi-company DMS setup
 * 
 * USAGE:
 * - Via browser: http://localhost/dmsnew/migration/run/COMPANY_ID
 * - Via CLI: php index.php migration run COMPANY_ID
 * 
 * IMPORTANT: Only Super Admin (role_id = 1) can run migrations
 */
class Migration extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        
        // Allow CLI access without login
        if (!$this->input->is_cli_request()) {
            $login_id = $this->session->userdata('id');
            $role_id = $this->session->userdata('role_id');
            
            if (empty($login_id)) {
                redirect('login');
                return;
            }
            
            // Only Super Admin can run migrations
            if ($role_id != '1') {
                show_error('Access Denied. Only Super Admin can run migrations.', 403);
                return;
            }
        }
    }

    /**
     * Main migration page
     */
    public function index()
    {
        $data['companies'] = $this->db->select('id, company_name')
            ->where('status', '1')
            ->order_by('company_name', 'ASC')
            ->get('company')
            ->result();
            
        $this->load->view('templates/header');
        $this->load->view('migration/index', $data);
    }

    /**
     * Run migration for a specific company
     * 
     * @param int $company_id Target company ID
     */
    public function run($company_id = null)
    {
        if (empty($company_id)) {
            $this->_output_error('Company ID is required');
            return;
        }

        // Verify company exists
        $company = $this->db->get_where('company', ['id' => $company_id])->row();
        if (!$company) {
            $this->_output_error('Company not found');
            return;
        }

        $results = [];
        $errors = [];

        try {
            // Start transaction
            $this->db->trans_begin();

            // Step 1: Migrate Authorities
            $results['authorities'] = $this->_migrate_authorities($company_id);

            // Step 2: Migrate Documents
            $results['documents'] = $this->_migrate_documents($company_id);

            // Step 3: Migrate Document Heads
            $results['document_heads'] = $this->_migrate_document_heads($company_id);

            // Step 4: Migrate Mandatory Documents
            $results['mandatory_documents'] = $this->_migrate_mandatory_documents($company_id);

            // Step 5: Migrate Reminders
            $results['reminders'] = $this->_migrate_reminders($company_id);

            // Step 6: Update Uploaded Documents
            $results['uploaded_documents'] = $this->_update_uploaded_documents($company_id);

            if ($this->db->trans_status() === FALSE) {
                $this->db->trans_rollback();
                $this->_output_error('Migration failed. Transaction rolled back.');
                return;
            }

            $this->db->trans_commit();
            $this->_output_success($company, $results);

        } catch (Exception $e) {
            $this->db->trans_rollback();
            $this->_output_error('Migration error: ' . $e->getMessage());
        }
    }

    /**
     * Migrate authorities to company_authorities
     */
    private function _migrate_authorities($company_id)
    {
        $sql = "
            INSERT INTO company_authorities (company_id, authority_id, is_enabled, created_at)
            SELECT 
                ?,
                id,
                TRUE,
                CURRENT_TIMESTAMP
            FROM authority
            WHERE status = '1' AND is_delete = '0'
            ON CONFLICT (company_id, authority_id) DO UPDATE 
            SET is_enabled = TRUE, updated_at = CURRENT_TIMESTAMP
        ";
        $this->db->query($sql, [$company_id]);

        return $this->db->select('COUNT(*) as count')
            ->where('company_id', $company_id)
            ->get('company_authorities')
            ->row()->count;
    }

    /**
     * Migrate documents to company_documents
     */
    private function _migrate_documents($company_id)
    {
        $sql = "
            INSERT INTO company_documents (company_id, document_id, is_enabled, created_at)
            SELECT 
                ?,
                id,
                TRUE,
                CURRENT_TIMESTAMP
            FROM documents
            WHERE status = '1'
            ON CONFLICT (company_id, document_id) DO UPDATE 
            SET is_enabled = TRUE, updated_at = CURRENT_TIMESTAMP
        ";
        $this->db->query($sql, [$company_id]);

        return $this->db->select('COUNT(*) as count')
            ->where('company_id', $company_id)
            ->get('company_documents')
            ->row()->count;
    }

    /**
     * Migrate document heads (sub_type) to company_document_heads
     */
    private function _migrate_document_heads($company_id)
    {
        $sql = "
            INSERT INTO company_document_heads (
                company_id, 
                type_id, 
                is_enabled, 
                is_reviewed,
                custom_start_date,
                custom_frequency_start_date,
                custom_due_in_same_next_month,
                enabled_at,
                created_at
            )
            SELECT 
                ?,
                id,
                TRUE,
                TRUE,
                document_start_date::date,
                frequency_start_date,
                due_in_same_next_month::text,
                CURRENT_TIMESTAMP,
                CURRENT_TIMESTAMP
            FROM sub_type
            WHERE status = '1'
            ON CONFLICT (company_id, type_id) DO UPDATE 
            SET 
                is_enabled = TRUE, 
                is_reviewed = TRUE,
                updated_at = CURRENT_TIMESTAMP
        ";
        $this->db->query($sql, [$company_id]);

        return $this->db->select('COUNT(*) as count')
            ->where('company_id', $company_id)
            ->get('company_document_heads')
            ->row()->count;
    }

    /**
     * Migrate mandatory documents to company_mandatory_documents
     */
    private function _migrate_mandatory_documents($company_id)
    {
        $sql = "
            INSERT INTO company_mandatory_documents (
                company_id, 
                type_id, 
                document_id, 
                is_mandatory, 
                is_enabled,
                created_at
            )
            SELECT 
                ?,
                type_id,
                document_id,
                CASE 
                    WHEN LOWER(mandatory) IN ('yes', '1', 'true', 't') THEN TRUE
                    ELSE FALSE
                END,
                TRUE,
                CURRENT_TIMESTAMP
            FROM mandatory_documents
            ON CONFLICT (company_id, type_id, document_id) DO UPDATE 
            SET 
                is_mandatory = EXCLUDED.is_mandatory,
                is_enabled = TRUE,
                updated_at = CURRENT_TIMESTAMP
        ";
        $this->db->query($sql, [$company_id]);

        return $this->db->select('COUNT(*) as count')
            ->where('company_id', $company_id)
            ->get('company_mandatory_documents')
            ->row()->count;
    }

    /**
     * Migrate reminders to company_document_head_reminders
     */
    private function _migrate_reminders($company_id)
    {
        $sql = "
            INSERT INTO company_document_head_reminders (
                company_id, 
                type_id, 
                reminder_no, 
                days_before, 
                reminder_to_user, 
                reminder_to_admin, 
                reminder_to_super_admin,
                is_enabled,
                created_at
            )
            SELECT 
                ?,
                sub_type_id,
                reminder_no,
                days_before,
                COALESCE(reminder_to_user::boolean, FALSE),
                COALESCE(reminder_to_admin::boolean, FALSE),
                COALESCE(reminder_to_super_admin::boolean, FALSE),
                TRUE,
                CURRENT_TIMESTAMP
            FROM sub_type_reminders
            ON CONFLICT (company_id, type_id, reminder_no) DO UPDATE 
            SET 
                days_before = EXCLUDED.days_before,
                reminder_to_user = EXCLUDED.reminder_to_user,
                reminder_to_admin = EXCLUDED.reminder_to_admin,
                reminder_to_super_admin = EXCLUDED.reminder_to_super_admin,
                is_enabled = TRUE,
                updated_at = CURRENT_TIMESTAMP
        ";
        $this->db->query($sql, [$company_id]);

        return $this->db->select('COUNT(*) as count')
            ->where('company_id', $company_id)
            ->get('company_document_head_reminders')
            ->row()->count;
    }

    /**
     * Update uploaded documents with company ID if not set
     */
    private function _update_uploaded_documents($company_id)
    {
        $this->db->where('company_id IS NULL OR company_id = 0', null, false)
            ->update('uploaded_documents', ['company_id' => $company_id]);

        return $this->db->affected_rows();
    }

    /**
     * Output success response
     */
    private function _output_success($company, $results)
    {
        if ($this->input->is_cli_request()) {
            echo "\n";
            echo "======================================\n";
            echo " Migration Completed Successfully!\n";
            echo "======================================\n";
            echo " Company: {$company->company_name} (ID: {$company->id})\n";
            echo "--------------------------------------\n";
            foreach ($results as $table => $count) {
                echo " {$table}: {$count} records\n";
            }
            echo "======================================\n\n";
        } else {
            $data = [
                'success' => true,
                'company' => $company,
                'results' => $results
            ];
            $this->load->view('templates/header');
            $this->load->view('migration/success', $data);
        }
    }

    /**
     * Output error response
     */
    private function _output_error($message)
    {
        if ($this->input->is_cli_request()) {
            echo "\nError: {$message}\n\n";
        } else {
            $this->session->set_flashdata('message_type', 'error');
            $this->session->set_flashdata('message_name', $message);
            redirect('migration');
        }
    }

    /**
     * Reset migration for a company (removes company-specific data)
     * USE WITH CAUTION - only for testing!
     */
    public function reset($company_id = null)
    {
        if (empty($company_id)) {
            $this->_output_error('Company ID is required');
            return;
        }

        // Confirm action (only via POST)
        if ($this->input->method() !== 'post' && !$this->input->is_cli_request()) {
            $company = $this->db->get_where('company', ['id' => $company_id])->row();
            $data = ['company' => $company];
            $this->load->view('templates/header');
            $this->load->view('migration/confirm_reset', $data);
            return;
        }

        try {
            $this->db->trans_begin();

            // Delete company-specific data
            $this->db->where('company_id', $company_id)->delete('company_document_head_reminders');
            $this->db->where('company_id', $company_id)->delete('company_mandatory_documents');
            $this->db->where('company_id', $company_id)->delete('company_document_heads');
            $this->db->where('company_id', $company_id)->delete('company_documents');
            $this->db->where('company_id', $company_id)->delete('company_authorities');

            if ($this->db->trans_status() === FALSE) {
                $this->db->trans_rollback();
                $this->_output_error('Reset failed. Transaction rolled back.');
                return;
            }

            $this->db->trans_commit();

            if ($this->input->is_cli_request()) {
                echo "\nMigration data reset for company ID: {$company_id}\n\n";
            } else {
                $this->session->set_flashdata('message_type', 'success');
                $this->session->set_flashdata('message_name', 'Migration data reset successfully');
                redirect('migration');
            }

        } catch (Exception $e) {
            $this->db->trans_rollback();
            $this->_output_error('Reset error: ' . $e->getMessage());
        }
    }
}

