<?php
/**
 * View Supplies - AUTO-DETECTING COLUMN NAMES
 * Works with any fish_types table structure
 */

ini_set('display_errors', 0);
error_reporting(E_ALL);

try {
    require_once __DIR__ . '/../config/database.php';
    require_once __DIR__ . '/../config/settings.php';
    require_once __DIR__ . '/../includes/functions.php';
} catch (Exception $e) {
    die("Error loading required files: " . $e->getMessage());
}

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

// Auto-detect fish_types column names
$fish_name_col = 'fish_name';
$fish_tamil_col = null;

try {
    $conn = getDbConnection();
    $result = $conn->query("DESCRIBE fish_types");
    $columns = [];
    while ($row = $result->fetch_assoc()) {
        $columns[] = $row['Field'];
    }
    
    // Check for English name column
    if (in_array('fish_name_english', $columns)) {
        $fish_name_col = 'fish_name_english';
    } elseif (in_array('fish_name', $columns)) {
        $fish_name_col = 'fish_name';
    } elseif (in_array('name', $columns)) {
        $fish_name_col = 'name';
    }
    
    // Check for Tamil name column
    if (in_array('fish_name_tamil', $columns)) {
        $fish_tamil_col = 'fish_name_tamil';
    } elseif (in_array('tamil_name', $columns)) {
        $fish_tamil_col = 'tamil_name';
    }
} catch (Exception $e) {
    // Use defaults
}

// Get current user info
$user_role = $_SESSION['role'];
$user_id = $_SESSION['user_id'];

// Pagination
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$per_page = 50;
$offset = ($page - 1) * $per_page;

// Filters
$from_date = $_GET['from_date'] ?? '';
$to_date = $_GET['to_date'] ?? '';
$fish_filter = $_GET['fish_id'] ?? '';
$supplier_filter = $_GET['supplier_id'] ?? '';

// Build query based on role
$where_clauses = [];
$params = [];
$types = "";

if ($user_role === 'supplier') {
    $where_clauses[] = "s.supplier_id = ?";
    $params[] = $user_id;
    $types .= "i";
}

if ($from_date) {
    $where_clauses[] = "s.supply_date >= ?";
    $params[] = $from_date;
    $types .= "s";
}

if ($to_date) {
    $where_clauses[] = "s.supply_date <= ?";
    $params[] = $to_date;
    $types .= "s";
}

if ($fish_filter) {
    $where_clauses[] = "s.fish_id = ?";
    $params[] = $fish_filter;
    $types .= "i";
}

if ($supplier_filter && hasRole(['admin', 'manager'])) {
    $where_clauses[] = "s.supplier_id = ?";
    $params[] = $supplier_filter;
    $types .= "i";
}

$where_sql = !empty($where_clauses) ? "WHERE " . implode(" AND ", $where_clauses) : "";

// Get total count
try {
    $count_sql = "SELECT COUNT(*) as total FROM supply s $where_sql";
    $count_result = !empty($params) ? fetchSingle($count_sql, $types, $params) : fetchSingle($count_sql);
    $total_records = $count_result ? $count_result['total'] : 0;
    $total_pages = ceil($total_records / $per_page);
} catch (Exception $e) {
    $total_records = 0;
    $total_pages = 0;
    $error = "Error counting records: " . $e->getMessage();
}

// Build fish columns for query
$fish_select = "f.$fish_name_col as fish_name";
if ($fish_tamil_col) {
    $fish_select .= ", f.$fish_tamil_col as fish_name_tamil";
}

// Get supplies
try {
    $sql = "
        SELECT s.*, 
               u.full_name as supplier_name,
               u.role as supplier_role,
               $fish_select
        FROM supply s
        JOIN users u ON s.supplier_id = u.user_id
        JOIN fish_types f ON s.fish_id = f.fish_id
        $where_sql
        ORDER BY s.supply_date DESC, s.created_at DESC
        LIMIT $per_page OFFSET $offset
    ";
    
    $supplies = !empty($params) ? fetchAll($sql, $types, $params) : fetchAll($sql);
} catch (Exception $e) {
    $supplies = [];
    $error = "Error loading supplies: " . $e->getMessage();
}

// Get summary
try {
    $summary_sql = "
        SELECT 
            SUM(weight_kg) as total_weight,
            SUM(total_amount) as total_amount
        FROM supply s $where_sql
    ";
    $summary_result = !empty($params) ? fetchSingle($summary_sql, $types, $params) : fetchSingle($summary_sql);
    $total_weight = $summary_result ? floatval($summary_result['total_weight']) : 0;
    $total_amount = $summary_result ? floatval($summary_result['total_amount']) : 0;
} catch (Exception $e) {
    $total_weight = 0;
    $total_amount = 0;
}

// Get fish types for filter
try {
    $fish_types = fetchAll("SELECT fish_id, $fish_name_col as fish_name FROM fish_types WHERE status = 'active' ORDER BY $fish_name_col");
} catch (Exception $e) {
    $fish_types = [];
}

// Get suppliers for filter
$suppliers = [];
if (hasRole(['admin', 'manager'])) {
    try {
        $suppliers = fetchAll("SELECT user_id, full_name, role FROM users WHERE (role = 'supplier' OR role = 'manager') AND status = 'active' ORDER BY full_name");
    } catch (Exception $e) {
        $suppliers = [];
    }
}

// Success/error messages
$success_message = '';
$error_message = isset($error) ? $error : '';
if (isset($_GET['success'])) {
    if ($_GET['success'] === 'deleted') {
        $success_message = 'Supply entry deleted successfully!';
    }
}
if (isset($_GET['error'])) {
    if ($_GET['error'] === 'invalid') {
        $error_message = 'Invalid supply entry ID';
    } elseif ($_GET['error'] === 'notfound') {
        $error_message = 'Supply entry not found';
    } elseif ($_GET['error'] === 'permission') {
        $error_message = 'You do not have permission to delete this entry';
    }
}

$page_title = 'View Supplies';
require_once __DIR__ . '/../includes/header.php';
?>

<h1 class="page-title">View All Supplies</h1>

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

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

<!-- Filters -->
<div class="card">
    <h2 class="card-title">Filter Supplies</h2>
    <form method="GET" action="">
        <div class="form-row">
            <div class="form-group">
                <label for="from_date">From Date</label>
                <input type="date" id="from_date" name="from_date" class="form-control" value="<?php echo htmlspecialchars($from_date); ?>">
            </div>
            <div class="form-group">
                <label for="to_date">To Date</label>
                <input type="date" id="to_date" name="to_date" class="form-control" value="<?php echo htmlspecialchars($to_date); ?>">
            </div>
            <div class="form-group">
                <label for="fish_id">Fish Type</label>
                <select id="fish_id" name="fish_id" class="form-control">
                    <option value="">All Fish Types</option>
                    <?php foreach ($fish_types as $fish): ?>
                    <option value="<?php echo $fish['fish_id']; ?>" <?php echo $fish_filter == $fish['fish_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($fish['fish_name']); ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <?php if (hasRole(['admin', 'manager'])): ?>
            <div class="form-group">
                <label for="supplier_id">Supplier</label>
                <select id="supplier_id" name="supplier_id" class="form-control">
                    <option value="">All Suppliers</option>
                    <?php foreach ($suppliers as $sup): ?>
                    <option value="<?php echo $sup['user_id']; ?>" <?php echo $supplier_filter == $sup['user_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($sup['full_name']); ?>
                        <?php if ($sup['role'] === 'manager'): ?>(Manager)<?php endif; ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <?php endif; ?>
            <div class="form-group" style="display: flex; align-items: flex-end; gap: 0.5rem;">
                <button type="submit" class="btn btn-primary">Apply Filters</button>
                <a href="view_supplies.php" class="btn btn-secondary">Clear</a>
            </div>
        </div>
    </form>
</div>

<!-- Summary Cards -->
<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem; margin-bottom: 2rem;">
    <div class="stat-card" style="background: linear-gradient(135deg, #17a2b8, #20B2AA);">
        <div class="stat-label">Total Weight</div>
        <div class="stat-value"><?php echo number_format($total_weight, 3); ?> kg</div>
        <div class="stat-info"><?php echo $total_records; ?> entries</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, #28a745, #20c997);">
        <div class="stat-label">Total Amount</div>
        <div class="stat-value">₹<?php echo number_format($total_amount, 2); ?></div>
        <div class="stat-info">Supply value</div>
    </div>
</div>

<!-- Supplies Table -->
<div class="card">
    <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 1rem;">
        <h2 class="card-title" style="margin: 0;">Supply Records</h2>
        <a href="add_supply.php" class="btn btn-success btn-sm">➕ Add Supply</a>
    </div>
    
    <?php if (!empty($supplies)): ?>
    <div class="table-responsive">
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Supplier</th>
                    <th>Fish Type</th>
                    <th class="text-right">Weight (kg)</th>
                    <th class="text-right">Rate/kg</th>
                    <th class="text-right">Total Amount</th>
                    <th>Notes</th>
                    <th class="text-center">Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $page_total = 0;
                foreach ($supplies as $supply): 
                    $page_total += $supply['total_amount'];
                    $date_obj = date_create($supply['supply_date']);
                ?>
                <tr>
                    <td><?php echo $date_obj ? $date_obj->format('d-m-Y') : $supply['supply_date']; ?></td>
                    <td>
                        <?php echo htmlspecialchars($supply['supplier_name']); ?>
                        <?php if ($supply['supplier_role'] === 'manager'): ?>
                            <span class="badge badge-info">Manager</span>
                        <?php endif; ?>
                    </td>
                    <td>
                        <?php echo htmlspecialchars($supply['fish_name']); ?>
                        <?php if (isset($supply['fish_name_tamil']) && $supply['fish_name_tamil']): ?>
                            <br><small style="color: #666;"><?php echo htmlspecialchars($supply['fish_name_tamil']); ?></small>
                        <?php endif; ?>
                    </td>
                    <td class="text-right"><?php echo number_format($supply['weight_kg'], 3); ?></td>
                    <td class="text-right">₹<?php echo number_format($supply['rate_per_kg'], 2); ?></td>
                    <td class="text-right"><strong>₹<?php echo number_format($supply['total_amount'], 2); ?></strong></td>
                    <td><?php 
                        if ($supply['notes']) {
                            echo htmlspecialchars(substr($supply['notes'], 0, 30));
                            if (strlen($supply['notes']) > 30) echo '...';
                        } else {
                            echo '<span style="color: #999;">-</span>';
                        }
                    ?></td>
                    <td class="text-center">
                        <a href="edit_supply.php?id=<?php echo $supply['supply_id']; ?>" 
                           class="btn btn-info btn-sm" title="Edit">✏️</a>
                        <a href="delete_supply.php?id=<?php echo $supply['supply_id']; ?>" 
                           class="btn btn-danger btn-sm" title="Delete"
                           onclick="return confirm('Are you sure you want to delete this supply entry? This cannot be undone!')">🗑️</a>
                    </td>
                </tr>
                <?php endforeach; ?>
                <tr style="background: #f8f9fa; font-weight: bold;">
                    <td colspan="5" class="text-right">Page Total:</td>
                    <td class="text-right">₹<?php echo number_format($page_total, 2); ?></td>
                    <td colspan="2"></td>
                </tr>
                <?php if ($total_pages > 1): ?>
                <tr style="background: #fff3cd; font-weight: bold;">
                    <td colspan="5" class="text-right">Grand Total:</td>
                    <td class="text-right">₹<?php echo number_format($total_amount, 2); ?></td>
                    <td colspan="2"></td>
                </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
    
    <!-- Pagination -->
    <?php if ($total_pages > 1): ?>
    <div style="display: flex; justify-content: center; align-items: center; margin-top: 1rem; padding-top: 1rem; border-top: 1px solid #dee2e6; gap: 1rem;">
        <?php if ($page > 1): ?>
        <a href="?page=<?php echo ($page - 1); ?><?php echo $from_date ? '&from_date=' . $from_date : ''; ?><?php echo $to_date ? '&to_date=' . $to_date : ''; ?><?php echo $fish_filter ? '&fish_id=' . $fish_filter : ''; ?><?php echo $supplier_filter ? '&supplier_id=' . $supplier_filter : ''; ?>" class="btn btn-secondary btn-sm">← Previous</a>
        <?php endif; ?>
        
        <span>Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
        
        <?php if ($page < $total_pages): ?>
        <a href="?page=<?php echo ($page + 1); ?><?php echo $from_date ? '&from_date=' . $from_date : ''; ?><?php echo $to_date ? '&to_date=' . $to_date : ''; ?><?php echo $fish_filter ? '&fish_id=' . $fish_filter : ''; ?><?php echo $supplier_filter ? '&supplier_id=' . $supplier_filter : ''; ?>" class="btn btn-secondary btn-sm">Next →</a>
        <?php endif; ?>
    </div>
    <?php endif; ?>
    
    <?php else: ?>
    <div class="empty-state">
        <div class="empty-state-icon">📦</div>
        <h3>No supply records found</h3>
        <p>
            <?php if ($from_date || $to_date || $fish_filter || $supplier_filter): ?>
                No supplies found matching your filters. Try adjusting the date range or filters.
            <?php else: ?>
                Start by adding your first supply entry
            <?php endif; ?>
        </p>
        <a href="add_supply.php" class="btn btn-success">➕ Add Supply</a>
    </div>
    <?php endif; ?>
</div>

<style>
.badge {
    display: inline-block;
    padding: 0.25rem 0.6rem;
    font-size: 0.75rem;
    font-weight: 600;
    color: white;
    background: #17a2b8;
    border-radius: 3px;
    margin-left: 0.5rem;
}

.badge-info {
    background: #17a2b8;
}
</style>

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