<?php
/**
 * @version        1.14.0
 * @package        Joomla
 * @subpackage     Edocman
 * @author         Tuan Pham Ngoc
 * @copyright      Copyright (C) 2011 - 2025 Ossolution Team
 * @license        GNU/GPL, see LICENSE.php
 */

defined('_JEXEC') or die;

use Joomla\CMS\MVC\Model\BaseDatabaseModel;
use Joomla\CMS\Factory;
use Joomla\CMS\Language\Text;
use Joomla\Filesystem\File;
use Joomla\Filesystem\Folder;
use Joomla\Filesystem\Path;

class EdocmanModelExportdb extends OSModel
{
    protected $availableTables = array(
        'categories' => '#__edocman_categories',
        'documents' => '#__edocman_documents',
        'document_categories' => '#__edocman_document_category',
        'tags' => '#__edocman_tags',
        'document_tags' => '#__edocman_document_tags',
        'levels' => '#__edocman_levels'
    );
    
    /**
     * Get table statistics
     */
    public function getTableStatistics()
    {
        $db = $this->getDbo();
        $stats = array();
        
        foreach ($this->availableTables as $name => $table) {
            $query = $db->getQuery(true)
                ->select('COUNT(*)')
                ->from($table);
            $db->setQuery($query);
            $stats[$name] = (int) $db->loadResult();
        }
        return $stats;
    }
    
    /**
     * Export data to SQL file
     */
    public function exportData($options = array())
    {
        $db = $this->getDbo();
        
        try {
            $sql = $this->generateSQLExport($options);
            
            // Create temporary file
            $filename = 'edocman_export_' . date('Y-m-d_H-i-s') . '.sql';
            $tempPath = Factory::getApplication()->get('tmp_path');
            $filepath = Path::clean($tempPath . '/' . $filename);
            
            if (!File::write($filepath, $sql)) {
                throw new Exception('Could not write export file');
            }
            
            return array(
                'success' => true,
                'message' => Text::_('COM_EDOCMAN_EXPORT_SUCCESS'),
                'filename' => $filename,
                'filepath' => $filepath
            );
            
        } catch (Exception $e) {
            return array(
                'success' => false,
                'message' => Text::_('COM_EDOCMAN_EXPORT_ERROR') . ': ' . $e->getMessage()
            );
        }
    }
    
    /**
     * Generate SQL export content
     */
    protected function generateSQLExport($options)
    {
        $db = $this->getDbo();
        $sql = '';
        
        // Header
        $sql .= "-- Edocman Export\n";
        $sql .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
        $sql .= "-- Joomla version: " . JVERSION . "\n\n";
        
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n";
        $sql .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
        $sql .= "SET time_zone = \"+00:00\";\n\n";
        
        $exportTables = isset($options['tables']) ? $options['tables'] : array_keys($this->availableTables);
        $exportType = isset($options['export_type']) ? $options['export_type'] : 'both';
        
        foreach ($exportTables as $tableName) {
            if (!isset($this->availableTables[$tableName])) {
                continue;
            }
            
            $table = $this->availableTables[$tableName];
            $sql .= $this->exportTable($table, $exportType);
        }
        
        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        
        return $sql;
    }
    
    /**
     * Export single table
     */
    protected function exportTable($table, $exportType)
    {
        $db = $this->getDbo();
        $sql = "\n-- Table: $table\n";
        
        // Export structure
        if ($exportType === 'structure' || $exportType === 'both') {
            $sql .= $this->getTableStructure($table);
        }
        
        // Export data
        if ($exportType === 'data' || $exportType === 'both') {
            $sql .= $this->getTableData($table);
        }
        
        return $sql;
    }
    
    /**
     * Get table structure SQL
     */
    protected function getTableStructure($table)
    {
        $db = $this->getDbo();
        
        $query = "SHOW CREATE TABLE " . $db->quoteName($table);
        $db->setQuery($query);
        $result = $db->loadRow();
        
        if (!$result) {
            return '';
        }
        
        $sql = "DROP TABLE IF EXISTS " . $db->quoteName($table) . ";\n";
        $sql .= $result[1] . ";\n\n";
        
        return $sql;
    }
    
    /**
     * Get table data SQL
     */
    protected function getTableData($table)
    {
        $db = $this->getDbo();
        
        $query = $db->getQuery(true)
            ->select('*')
            ->from($table);
        $db->setQuery($query);
        $rows = $db->loadAssocList();
        
        if (empty($rows)) {
            return "-- No data for table $table\n\n";
        }
        
        $sql = "";
        $columns = array_keys($rows[0]);
        $columnList = implode('`, `', $columns);
        $columnList = '`' . $columnList . '`';
        
        foreach ($rows as $row) {
            $values = array();
            foreach ($row as $value) {
                if ($value === null) {
                    $values[] = 'NULL';
                } else {
                    $values[] = $db->quote($value);
                }
            }
            
            $sql .= "INSERT INTO " . $db->quoteName($table) . " ($columnList) VALUES (" . implode(', ', $values) . ");\n";
        }
        
        $sql .= "\n";
        return $sql;
    }
    
    /**
     * Get file attachments for export
     */
    public function getFileAttachments()
    {
        $db = $this->getDbo();
        $config = EDocmanHelper::loadConfig();
        
        $query = $db->getQuery(true)
            ->select('filename, original_filename, image')
            ->from('#__edocman_documents')
            ->where('filename != ""');
        
        $db->setQuery($query);
        $documents = $db->loadObjectList();
        
        $files = array();
        $documentsPath = $config->get('documents_path', JPATH_ROOT . '/media/com_edocman/documents');
        
        foreach ($documents as $doc) {
            if ($doc->filename) {
                $filepath = Path::clean($documentsPath . '/' . $doc->filename);
                if (file_exists($filepath)) {
                    $files[] = array(
                        'source' => $filepath,
                        'destination' => 'documents/' . $doc->filename
                    );
                }
            }
            
            if ($doc->image) {
                $imagePath = Path::clean(JPATH_ROOT . '/media/com_edocman/document/' . $doc->image);
                if (file_exists($imagePath)) {
                    $files[] = array(
                        'source' => $imagePath,
                        'destination' => 'images/documents/' . $doc->image
                    );
                }
            }
        }
        
        // Category images
        $query = $db->getQuery(true)
            ->select('image')
            ->from('#__edocman_categories')
            ->where('image != ""');
        
        $db->setQuery($query);
        $categories = $db->loadObjectList();
        
        foreach ($categories as $cat) {
            if ($cat->image) {
                $imagePath = Path::clean(JPATH_ROOT . '/media/com_edocman/category/' . $cat->image);
                if (file_exists($imagePath)) {
                    $files[] = array(
                        'source' => $imagePath,
                        'destination' => 'images/categories/' . $cat->image
                    );
                }
            }
        }
        
        return $files;
    }
}
