<?php
session_start();
require_once 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Set timezone to East Africa Time
date_default_timezone_set('Africa/Nairobi');

$user_id = $_SESSION['user_id'];
$success_message = '';
$error_message = '';

// Get company ID from URL
if (!isset($_GET['company_id']) || !is_numeric($_GET['company_id'])) {
    header('Location: list_insurers.php');
    exit();
}

$company_id = (int)$_GET['company_id'];

// Get company details
$company_sql = "SELECT * FROM insurance_companies WHERE company_id = ?";
$company_stmt = $conn->prepare($company_sql);
$company_stmt->bind_param("i", $company_id);
$company_stmt->execute();
$company = $company_stmt->get_result()->fetch_assoc();
$company_stmt->close();

if (!$company) {
    header('Location: list_insurers.php');
    exit();
}

// Pagination settings
$records_per_page = 15;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $records_per_page;

// Search and filter functionality
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$category_filter = isset($_GET['category']) ? $_GET['category'] : '';
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';

// Build WHERE clause
$where_conditions = ["ip.company_id = ?"];
$params = [$company_id];
$param_types = 'i';

if (!empty($search)) {
    $where_conditions[] = "(ip.product_name LIKE ? OR ip.product_code LIKE ? OR ip.description LIKE ?)";
    $search_param = "%{$search}%";
    $params[] = $search_param;
    $params[] = $search_param;
    $params[] = $search_param;
    $param_types .= 'sss';
}

if (!empty($category_filter)) {
    $where_conditions[] = "ip.category_id = ?";
    $params[] = (int)$category_filter;
    $param_types .= 'i';
}

if ($status_filter !== '') {
    $where_conditions[] = "ip.is_active = ?";
    $params[] = (int)$status_filter;
    $param_types .= 'i';
}

$where_clause = 'WHERE ' . implode(' AND ', $where_conditions);

// Get total count for pagination
$count_sql = "SELECT COUNT(*) as total 
              FROM insurance_products ip 
              LEFT JOIN product_categories pc ON ip.category_id = pc.category_id 
              {$where_clause}";
$count_stmt = $conn->prepare($count_sql);
$count_stmt->bind_param($param_types, ...$params);
$count_stmt->execute();
$total_records = $count_stmt->get_result()->fetch_assoc()['total'];
$count_stmt->close();

$total_pages = ceil($total_records / $records_per_page);

// Get products data
$sql = "SELECT 
    ip.*,
    pc.category_name,
    pc.category_code,
    COUNT(DISTINCT p.policy_id) as total_policies,
    COUNT(DISTINCT CASE WHEN p.status = 'ACTIVE' THEN p.policy_id END) as active_policies,
    SUM(CASE WHEN p.status = 'ACTIVE' THEN p.premium_amount ELSE 0 END) as total_premium_value,
    COUNT(DISTINCT p.customer_id) as unique_customers
FROM insurance_products ip
LEFT JOIN product_categories pc ON ip.category_id = pc.category_id
LEFT JOIN policies p ON ip.product_id = p.product_id
{$where_clause}
GROUP BY ip.product_id
ORDER BY ip.product_name ASC
LIMIT ? OFFSET ?";

$stmt = $conn->prepare($sql);
$params[] = $records_per_page;
$params[] = $offset;
$param_types .= 'ii';

$stmt->bind_param($param_types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

// Get categories for filter dropdown
$categories_sql = "SELECT DISTINCT pc.category_id, pc.category_name 
                   FROM product_categories pc 
                   INNER JOIN insurance_products ip ON pc.category_id = ip.category_id 
                   WHERE ip.company_id = ? 
                   ORDER BY pc.category_name";
$categories_stmt = $conn->prepare($categories_sql);
$categories_stmt->bind_param("i", $company_id);
$categories_stmt->execute();
$categories_result = $categories_stmt->get_result();

// Get company statistics
$stats_sql = "SELECT 
    COUNT(ip.product_id) as total_products,
    SUM(CASE WHEN ip.is_active = 1 THEN 1 ELSE 0 END) as active_products,
    COUNT(DISTINCT p.policy_id) as total_policies,
    SUM(CASE WHEN p.status = 'ACTIVE' THEN p.premium_amount ELSE 0 END) as total_premium_value
FROM insurance_products ip
LEFT JOIN policies p ON ip.product_id = p.product_id
WHERE ip.company_id = ?";

$stats_stmt = $conn->prepare($stats_sql);
$stats_stmt->bind_param("i", $company_id);
$stats_stmt->execute();
$stats = $stats_stmt->get_result()->fetch_assoc();
$stats_stmt->close();

$conn->close();
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => $company['company_name'] . ' Products - Ultrasure Insurance Platform')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <style>
        .company-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
            padding: 1.5rem;
            margin-bottom: 2rem;
        }
        
        .product-card {
            border: none;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            border-radius: 15px;
            transition: all 0.3s ease;
            margin-bottom: 1.5rem;
        }
        
        .product-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.15);
        }
        
        .stats-card {
            background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
            border-radius: 15px;
            border: none;
        }
        
        .search-card {
            border: none;
            box-shadow: 0 2px 10px rgba(0,0,0,0.08);
            border-radius: 15px;
        }
        
        .coverage-badge {
            font-size: 0.75rem;
            padding: 0.25rem 0.5rem;
        }
        
        .premium-display {
            font-size: 1.1rem;
            font-weight: 600;
            color: #28a745;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Company Products</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                        <li class="breadcrumb-item"><a href="list_insurers.php">Insurance Companies</a></li>
                                        <li class="breadcrumb-item"><a href="view_insurer.php?id=<?php echo $company_id; ?>"><?php echo htmlspecialchars($company['company_name']); ?></a></li>
                                        <li class="breadcrumb-item active">Products</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Company Header -->
                    <div class="row">
                        <div class="col-12">
                            <div class="company-header">
                                <div class="d-flex align-items-center justify-content-between">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-lg me-3">
                                            <span class="avatar-title bg-white text-primary rounded-circle fs-2">
                                                <?php echo strtoupper(substr($company['company_name'], 0, 2)); ?>
                                            </span>
                                        </div>
                                        <div>
                                            <h3 class="mb-1"><?php echo htmlspecialchars($company['company_name']); ?></h3>
                                            <p class="mb-0 opacity-75">Insurance Products Portfolio</p>
                                        </div>
                                    </div>
                                    <div class="text-end">
                                        <a href="products/add_product.php?company_id=<?php echo $company_id; ?>" 
                                           class="btn btn-light me-2">
                                            <i class="ri-add-line me-1"></i>Add Product
                                        </a>
                                        <a href="view_insurer.php?id=<?php echo $company_id; ?>" 
                                           class="btn btn-outline-light">
                                            <i class="ri-arrow-left-line me-1"></i>Back to Company
                                        </a>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Statistics Cards -->
                    <div class="row mb-4">
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-primary text-white rounded-circle">
                                                    <i class="ri-shield-check-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <h4 class="mb-1 text-primary"><?php echo number_format($stats['total_products']); ?></h4>
                                            <p class="mb-0 text-muted">Total Products</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-success text-white rounded-circle">
                                                    <i class="ri-checkbox-circle-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <h4 class="mb-1 text-success"><?php echo number_format($stats['active_products']); ?></h4>
                                            <p class="mb-0 text-muted">Active Products</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-info text-white rounded-circle">
                                                    <i class="ri-file-text-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <h4 class="mb-1 text-info"><?php echo number_format($stats['total_policies']); ?></h4>
                                            <p class="mb-0 text-muted">Total Policies</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-warning text-white rounded-circle">
                                                    <i class="ri-money-dollar-circle-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <h4 class="mb-1 text-warning">KSH <?php echo number_format($stats['total_premium_value'], 0); ?></h4>
                                            <p class="mb-0 text-muted">Premium Value</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Search and Filter -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="card search-card">
                                <div class="card-body">
                                    <form method="GET" class="row g-3">
                                        <input type="hidden" name="company_id" value="<?php echo $company_id; ?>">
                                        
                                        <div class="col-md-4">
                                            <div class="form-floating">
                                                <input type="text" class="form-control" id="search" name="search" 
                                                       placeholder="Search products..." 
                                                       value="<?php echo htmlspecialchars($search); ?>">
                                                <label for="search">Search Products</label>
                                            </div>
                                        </div>
                                        
                                        <div class="col-md-3">
                                            <div class="form-floating">
                                                <select class="form-select" id="category" name="category">
                                                    <option value="">All Categories</option>
                                                    <?php while($cat = $categories_result->fetch_assoc()): ?>
                                                        <option value="<?php echo $cat['category_id']; ?>" 
                                                                <?php echo $category_filter == $cat['category_id'] ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($cat['category_name']); ?>
                                                        </option>
                                                    <?php endwhile; ?>
                                                </select>
                                                <label for="category">Category Filter</label>
                                            </div>
                                        </div>
                                        
                                        <div class="col-md-2">
                                            <div class="form-floating">
                                                <select class="form-select" id="status" name="status">
                                                    <option value="">All Status</option>
                                                    <option value="1" <?php echo $status_filter === '1' ? 'selected' : ''; ?>>Active</option>
                                                    <option value="0" <?php echo $status_filter === '0' ? 'selected' : ''; ?>>Inactive</option>
                                                </select>
                                                <label for="status">Status</label>
                                            </div>
                                        </div>
                                        
                                        <div class="col-md-2">
                                            <button type="submit" class="btn btn-primary w-100 h-100">
                                                <i class="ri-search-line me-1"></i>Search
                                            </button>
                                        </div>
                                        
                                        <div class="col-md-1">
                                            <a href="?company_id=<?php echo $company_id; ?>" class="btn btn-outline-secondary w-100 h-100">
                                                <i class="ri-refresh-line"></i>
                                            </a>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Products Grid -->
                    <div class="row">
                        <?php if ($result->num_rows > 0): ?>
                            <?php while($product = $result->fetch_assoc()): ?>
                            <div class="col-xl-4 col-md-6">
                                <div class="card product-card">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-start mb-3">
                                            <div class="flex-grow-1">
                                                <h5 class="mb-1">
                                                    <a href="products/view_product.php?id=<?php echo $product['product_id']; ?>" 
                                                       class="text-decoration-none">
                                                        <?php echo htmlspecialchars($product['product_name']); ?>
                                                    </a>
                                                </h5>
                                                <p class="text-muted mb-2">
                                                    <span class="badge bg-soft-info text-info me-1"><?php echo htmlspecialchars($product['product_code']); ?></span>
                                                    <span class="badge bg-soft-secondary text-secondary"><?php echo htmlspecialchars($product['category_name']); ?></span>
                                                </p>
                                            </div>
                                            <span class="badge bg-<?php echo $product['is_active'] ? 'success' : 'secondary'; ?>">
                                                <?php echo $product['is_active'] ? 'Active' : 'Inactive'; ?>
                                            </span>
                                        </div>
                                        
                                        <div class="mb-3">
                                            <span class="badge coverage-badge bg-soft-primary text-primary">
                                                <?php echo ucfirst(strtolower($product['coverage_type'])); ?>
                                            </span>
                                            <span class="badge coverage-badge bg-soft-warning text-warning">
                                                <?php echo ucfirst(strtolower($product['product_type'])); ?>
                                            </span>
                                        </div>
                                        
                                        <?php if ($product['description']): ?>
                                        <p class="text-muted small mb-3">
                                            <?php echo htmlspecialchars(substr($product['description'], 0, 100)); ?>
                                            <?php if (strlen($product['description']) > 100): ?>...<?php endif; ?>
                                        </p>
                                        <?php endif; ?>
                                        
                                        <div class="row text-center mb-3">
                                            <div class="col-6">
                                                <div class="border-end">
                                                    <h6 class="mb-0 text-primary"><?php echo $product['active_policies']; ?></h6>
                                                    <small class="text-muted">Active Policies</small>
                                                </div>
                                            </div>
                                            <div class="col-6">
                                                <h6 class="mb-0 text-success"><?php echo $product['unique_customers']; ?></h6>
                                                <small class="text-muted">Customers</small>
                                            </div>
                                        </div>
                                        
                                        <?php if ($product['total_premium_value'] > 0): ?>
                                        <div class="text-center mb-3">
                                            <div class="premium-display">
                                                KSH <?php echo number_format($product['total_premium_value'], 0); ?>
                                            </div>
                                            <small class="text-muted">Total Premium Value</small>
                                        </div>
                                        <?php endif; ?>
                                        
                                        <div class="d-flex gap-2">
                                            <a href="products/view_product.php?id=<?php echo $product['product_id']; ?>" 
                                               class="btn btn-outline-primary btn-sm flex-fill">
                                                <i class="ri-eye-line me-1"></i>View
                                            </a>
                                            <a href="products/edit_product.php?id=<?php echo $product['product_id']; ?>" 
                                               class="btn btn-outline-info btn-sm flex-fill">
                                                <i class="ri-edit-line me-1"></i>Edit
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <?php endwhile; ?>
                            
                            <!-- Pagination -->
                            <?php if ($total_pages > 1): ?>
                            <div class="col-12">
                                <nav aria-label="Products pagination" class="mt-4">
                                    <ul class="pagination pagination-sm justify-content-center">
                                        <?php if ($page > 1): ?>
                                        <li class="page-item">
                                            <a class="page-link" href="?company_id=<?php echo $company_id; ?>&page=<?php echo $page-1; ?>&search=<?php echo urlencode($search); ?>&category=<?php echo $category_filter; ?>&status=<?php echo $status_filter; ?>">
                                                <i class="ri-arrow-left-line"></i>
                                            </a>
                                        </li>
                                        <?php endif; ?>
                                        
                                        <?php for ($i = max(1, $page - 2); $i <= min($total_pages, $page + 2); $i++): ?>
                                        <li class="page-item <?php echo $i == $page ? 'active' : ''; ?>">
                                            <a class="page-link" href="?company_id=<?php echo $company_id; ?>&page=<?php echo $i; ?>&search=<?php echo urlencode($search); ?>&category=<?php echo $category_filter; ?>&status=<?php echo $status_filter; ?>">
                                                <?php echo $i; ?>
                                            </a>
                                        </li>
                                        <?php endfor; ?>
                                        
                                        <?php if ($page < $total_pages): ?>
                                        <li class="page-item">
                                            <a class="page-link" href="?company_id=<?php echo $company_id; ?>&page=<?php echo $page+1; ?>&search=<?php echo urlencode($search); ?>&category=<?php echo $category_filter; ?>&status=<?php echo $status_filter; ?>">
                                                <i class="ri-arrow-right-line"></i>
                                            </a>
                                        </li>
                                        <?php endif; ?>
                                    </ul>
                                </nav>
                            </div>
                            <?php endif; ?>
                            
                        <?php else: ?>
                        <div class="col-12">
                            <div class="text-center py-5">
                                <div class="avatar-lg mx-auto mb-4">
                                    <div class="avatar-title bg-soft-primary text-primary rounded-circle">
                                        <i class="ri-shield-check-line fs-1"></i>
                                    </div>
                                </div>
                                <h5>No products found</h5>
                                <p class="text-muted">
                                    <?php if (!empty($search) || $category_filter !== '' || $status_filter !== ''): ?>
                                        No products match your search criteria. Try adjusting your filters.
                                    <?php else: ?>
                                        This company doesn't have any products yet.
                                    <?php endif; ?>
                                </p>
                                <a href="products/add_product.php?company_id=<?php echo $company_id; ?>" class="btn btn-primary">
                                    <i class="ri-add-line me-2"></i>Add First Product
                                </a>
                            </div>
                        </div>
                        <?php endif; ?>
                    </div>

                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <script>
        // Auto-submit form on filter changes
        document.getElementById('category').addEventListener('change', function() {
            this.form.submit();
        });
        
        document.getElementById('status').addEventListener('change', function() {
            this.form.submit();
        });
    </script>
    
    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>