<?php
/**
 * Manage Fish Types
 * Add, edit, and manage fish types
 * AUTO-DETECTS COLUMN NAMES
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/settings.php';
require_once __DIR__ . '/../includes/functions.php';

startSession();
requireLogin();
requireRole(['admin', 'manager']);

$success = '';
$error = '';

// Auto-detect column structure
$conn = getDbConnection();
$columns_result = $conn->query("DESCRIBE fish_types");
$columns = [];
while ($row = $columns_result->fetch_assoc()) {
    $columns[] = $row['Field'];
}

// Determine which columns exist
$id_col = in_array('fish_id', $columns) ? 'fish_id' : 'id';
$name_en_col = in_array('fish_name_english', $columns) ? 'fish_name_english' : 
               (in_array('fish_name', $columns) ? 'fish_name' : 'name');
$name_ta_col = in_array('fish_name_tamil', $columns) ? 'fish_name_tamil' : 
               (in_array('tamil_name', $columns) ? 'tamil_name' : null);
$status_col = in_array('status', $columns) ? 'status' : null;
$created_col = in_array('created_at', $columns) ? 'created_at' : null;

// Handle Add Fish Type
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'add_fish') {
    $fish_name = trim($_POST['fish_name']);
    $fish_name_tamil = trim($_POST['fish_name_tamil'] ?? '');
    
    if (empty($fish_name)) {
        $error = "Fish name is required";
    } else {
        try {
            // Check duplicate
            $check_sql = "SELECT $id_col FROM fish_types WHERE $name_en_col = ?";
            $stmt = $conn->prepare($check_sql);
            $stmt->bind_param("s", $fish_name);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                $error = "Fish type '$fish_name' already exists";
            } else {
                // Build INSERT query based on available columns
                $insert_cols = [$name_en_col];
                $insert_vals = [$fish_name];
                $types = "s";
                
                if ($name_ta_col && !empty($fish_name_tamil)) {
                    $insert_cols[] = $name_ta_col;
                    $insert_vals[] = $fish_name_tamil;
                    $types .= "s";
                }
                
                if ($status_col) {
                    $insert_cols[] = $status_col;
                    $insert_vals[] = 'active';
                    $types .= "s";
                }
                
                if ($created_col) {
                    $insert_cols[] = $created_col;
                    $insert_vals[] = date('Y-m-d H:i:s');
                    $types .= "s";
                }
                
                $cols_str = implode(', ', $insert_cols);
                $placeholders = implode(', ', array_fill(0, count($insert_cols), '?'));
                
                $sql = "INSERT INTO fish_types ($cols_str) VALUES ($placeholders)";
                $stmt = $conn->prepare($sql);
                
                if ($stmt) {
                    $stmt->bind_param($types, ...$insert_vals);
                    
                    if ($stmt->execute()) {
                        logActivity($_SESSION['user_id'], 'CREATE', 'fish_types', "Created fish type: $fish_name");
                        $success = "Fish type '$fish_name' created successfully!";
                    } else {
                        $error = "Failed to create fish type: " . $stmt->error;
                    }
                } else {
                    $error = "Database error: " . $conn->error;
                }
            }
        } catch (Exception $e) {
            $error = "Error: " . $e->getMessage();
        }
    }
}

// Handle Toggle Status (only if status column exists)
if ($status_col && isset($_GET['toggle'])) {
    $fish_id = intval($_GET['toggle']);
    try {
        $sql = "UPDATE fish_types SET $status_col = IF($status_col = 'active', 'inactive', 'active') WHERE $id_col = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $fish_id);
        $stmt->execute();
        logActivity($_SESSION['user_id'], 'UPDATE', 'fish_types', "Toggled fish type status (ID: $fish_id)");
        $success = "Fish type status updated";
    } catch (Exception $e) {
        $error = "Error: " . $e->getMessage();
    }
}

// Get all fish types
try {
    $select_cols = [$id_col, $name_en_col];
    if ($name_ta_col) $select_cols[] = $name_ta_col;
    if ($status_col) $select_cols[] = $status_col;
    
    $cols_str = implode(', ', $select_cols);
    $fish_types = fetchAll("SELECT $cols_str FROM fish_types ORDER BY $name_en_col");
} catch (Exception $e) {
    $fish_types = [];
    $error = "Error loading fish types: " . $e->getMessage();
}

$page_title = 'Manage Fish Types';
require_once __DIR__ . '/../includes/header.php';
?>

<h1 class="page-title">🐟 Manage Fish Types</h1>

<?php if ($success): ?>
<div class="alert alert-success"><?php echo htmlspecialchars($success); ?></div>
<?php endif; ?>

<?php if ($error): ?>
<div class="alert alert-error"><?php echo htmlspecialchars($error); ?></div>
<?php endif; ?>

<div style="display: grid; grid-template-columns: 1fr 1fr; gap: 2rem; margin-bottom: 2rem;">
    <!-- Add Fish Type Form -->
    <div class="card">
        <h2 class="card-title">➕ Add New Fish Type</h2>
        <form method="POST" action="">
            <input type="hidden" name="action" value="add_fish">
            
            <div class="form-group">
                <label for="fish_name">Fish Name (English) *</label>
                <input type="text" id="fish_name" name="fish_name" class="form-control" 
                       required autofocus placeholder="e.g., Tuna, Mackerel, Sardine">
            </div>
            
            <?php if ($name_ta_col): ?>
            <div class="form-group">
                <label for="fish_name_tamil">Fish Name (Tamil)</label>
                <input type="text" id="fish_name_tamil" name="fish_name_tamil" class="form-control" 
                       placeholder="e.g., சூரை, அயிலை, மத்தி">
            </div>
            <?php endif; ?>
            
            <div class="btn-group">
                <button type="submit" class="btn btn-success">💾 Add Fish Type</button>
                <button type="reset" class="btn btn-secondary">🔄 Clear</button>
            </div>
        </form>
    </div>
    
    <!-- Existing Fish Types -->
    <div class="card">
        <h2 class="card-title">All Fish Types (<?php echo count($fish_types); ?>)</h2>
        
        <?php if (!empty($fish_types)): ?>
        <div style="max-height: 400px; overflow-y: auto;">
            <table>
                <thead>
                    <tr>
                        <th>Fish Name</th>
                        <?php if ($status_col): ?>
                        <th class="text-center">Status</th>
                        <th class="text-center">Actions</th>
                        <?php endif; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($fish_types as $fish): ?>
                    <tr>
                        <td>
                            <strong><?php echo htmlspecialchars($fish[$name_en_col]); ?></strong>
                            <?php if ($name_ta_col && !empty($fish[$name_ta_col])): ?>
                                <br><small style="color: #666;"><?php echo htmlspecialchars($fish[$name_ta_col]); ?></small>
                            <?php endif; ?>
                        </td>
                        <?php if ($status_col): ?>
                        <td class="text-center">
                            <?php if ($fish[$status_col] === 'active'): ?>
                                <span class="badge badge-success">Active</span>
                            <?php else: ?>
                                <span class="badge badge-danger">Inactive</span>
                            <?php endif; ?>
                        </td>
                        <td class="text-center">
                            <a href="?toggle=<?php echo $fish[$id_col]; ?>" 
                               class="btn btn-sm btn-warning"
                               onclick="return confirm('Toggle fish type status?')">
                                <?php echo $fish[$status_col] === 'active' ? '⏸️ Deactivate' : '▶️ Activate'; ?>
                            </a>
                        </td>
                        <?php endif; ?>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <?php else: ?>
        <div class="empty-state">
            <div class="empty-state-icon">🐟</div>
            <h3>No fish types yet</h3>
            <p>Add your first fish type using the form on the left</p>
        </div>
        <?php endif; ?>
    </div>
</div>

<!-- Common Fish Types Reference -->
<div class="card" style="background: #f8f9fa;">
    <h3 style="margin-top: 0; color: #008B8B;">💡 Common Fish Types in Tamil Nadu</h3>
    <p style="margin-bottom: 1rem; color: #666;">Here are some common fish types you can add:</p>
    <div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 1rem;">
        <?php
        $common_fish = [
            ['english' => 'Tuna', 'tamil' => 'சூரை (Soorai)'],
            ['english' => 'Mackerel', 'tamil' => 'அயிலை (Ayilai)'],
            ['english' => 'Sardine', 'tamil' => 'மத்தி (Mathi)'],
            ['english' => 'Pomfret', 'tamil' => 'வாவல் (Vaaval)'],
            ['english' => 'King Fish', 'tamil' => 'நெய்மீன் (Neimeen)'],
            ['english' => 'Prawns', 'tamil' => 'இறால் (Iraal)'],
            ['english' => 'Crab', 'tamil' => 'நண்டு (Nandu)'],
            ['english' => 'Squid', 'tamil' => 'கணவாய் (Kanavai)'],
            ['english' => 'Anchovy', 'tamil' => 'நெத்திலி (Nethili)'],
            ['english' => 'Red Snapper', 'tamil' => 'சங்கரா (Sangara)'],
            ['english' => 'Seer Fish', 'tamil' => 'வஞ்சிரம் (Vanjiram)'],
            ['english' => 'Pomfret White', 'tamil' => 'வெள்ளை வாவல் (Vellai Vaaval)'],
        ];
        
        foreach ($common_fish as $fish): ?>
        <div style="padding: 0.75rem; background: white; border-radius: 5px; border-left: 3px solid #008B8B;">
            <strong style="display: block; margin-bottom: 0.25rem; color: #333;">
                <?php echo $fish['english']; ?>
            </strong>
            <small style="color: #666;"><?php echo $fish['tamil']; ?></small>
        </div>
        <?php endforeach; ?>
    </div>
</div>

<!-- Info Box -->
<div class="card" style="background: #d1ecf1; border-left: 4px solid #17a2b8;">
    <h3 style="margin-top: 0; color: #0c5460;">ℹ️ Table Structure Detected</h3>
    <p style="margin: 0; color: #0c5460;">
        <strong>Columns found in your fish_types table:</strong><br>
        ID Column: <code><?php echo $id_col; ?></code><br>
        Name Column: <code><?php echo $name_en_col; ?></code><br>
        <?php if ($name_ta_col): ?>
        Tamil Name Column: <code><?php echo $name_ta_col; ?></code><br>
        <?php endif; ?>
        <?php if ($status_col): ?>
        Status Column: <code><?php echo $status_col; ?></code><br>
        <?php endif; ?>
    </p>
</div>

<?php if ($status_col): ?>
<!-- Info Box -->
<div class="card" style="background: #fff3cd; border-left: 4px solid #ffc107;">
    <h3 style="margin-top: 0; color: #856404;">ℹ️ Important Notes</h3>
    <ul style="margin: 0; padding-left: 1.5rem; color: #856404;">
        <li><strong>Active fish types</strong> appear in the supply entry dropdown</li>
        <li><strong>Inactive fish types</strong> are hidden but data is preserved</li>
        <li><strong>Cannot delete</strong> fish types that have existing supply entries</li>
        <?php if ($name_ta_col): ?>
        <li><strong>Tamil names</strong> are optional but helpful for local staff</li>
        <?php endif; ?>
        <li><strong>Toggle status</strong> instead of deleting to maintain data integrity</li>
    </ul>
</div>
<?php endif; ?>

<?php require_once __DIR__ . '/../includes/footer.php'; ?>