<?php
session_start();
require_once 'layouts/dbconn.php';
//require_once 'includes/functions.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');

// Get user details
$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role_name'];

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

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

// Build WHERE clause
$where_conditions = [];
$params = [];
$param_types = '';

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[] = $category_filter;
    $param_types .= 'i';
}

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

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

$where_clause = !empty($where_conditions) ? '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
              LEFT JOIN insurance_companies ic ON ip.company_id = ic.company_id
              $where_clause";

if (!empty($params)) {
    $count_stmt = $conn->prepare($count_sql);
    if (!empty($param_types)) {
        $count_stmt->bind_param($param_types, ...$params);
    }
    $count_stmt->execute();
    $total_records = $count_stmt->get_result()->fetch_assoc()['total'];
    $count_stmt->close();
} else {
    $total_records = $conn->query($count_sql)->fetch_assoc()['total'];
}

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

// Main query to fetch products
$sql = "SELECT 
    ip.product_id,
    ip.product_code,
    ip.product_name,
    ip.product_type,
    ip.coverage_type,
    ip.description,
    ip.age_range_min,
    ip.age_range_max,
    ip.sum_assured_min,
    ip.sum_assured_max,
    ip.base_premium,
    ip.commission_rate,
    ip.is_active,
    ip.launch_date,
    ip.created_at,
    pc.category_name,
    pc.category_code,
    ic.company_name,
    ic.company_code,
    COUNT(p.policy_id) as active_policies,
    COALESCE(SUM(p.premium_amount), 0) as total_premium_volume
FROM insurance_products ip
LEFT JOIN product_categories pc ON ip.category_id = pc.category_id
LEFT JOIN insurance_companies ic ON ip.company_id = ic.company_id
LEFT JOIN policies p ON ip.product_id = p.product_id AND p.status = 'ACTIVE'
$where_clause
GROUP BY ip.product_id, ip.product_code, ip.product_name, ip.product_type, ip.coverage_type, 
         ip.description, ip.age_range_min, ip.age_range_max, ip.sum_assured_min, ip.sum_assured_max,
         ip.base_premium, ip.commission_rate, ip.is_active, ip.launch_date, ip.created_at,
         pc.category_name, pc.category_code, ic.company_name, ic.company_code
ORDER BY ip.created_at DESC
LIMIT ? OFFSET ?";

// Add pagination parameters
$params[] = $records_per_page;
$params[] = $offset;
$param_types .= 'ii';

$stmt = $conn->prepare($sql);
if (!empty($param_types)) {
    $stmt->bind_param($param_types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

// Get categories for filter dropdown
$categories_sql = "SELECT category_id, category_name FROM product_categories WHERE is_active = 1 ORDER BY category_name";
$categories_result = $conn->query($categories_sql);

// Get companies for filter dropdown
$companies_sql = "SELECT company_id, company_name FROM insurance_companies WHERE is_active = 1 ORDER BY company_name";
$companies_result = $conn->query($companies_sql);

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Insurance Products - Ultrasure Insurance Platform')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- DataTables CSS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap5.min.css">
    <!-- Select2 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <style>
        .product-card {
            transition: all 0.3s ease;
            border: none;
            box-shadow: 0 2px 10px rgba(0,0,0,0.08);
        }
        
        .product-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.15);
        }
        
        .coverage-badge {
            font-size: 0.75rem;
            padding: 0.25rem 0.5rem;
            border-radius: 0.375rem;
        }
        
        .stats-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
        }
        
        .filter-card {
            background: #f8f9fa;
            border-radius: 15px;
            border: none;
        }
        
        .premium-range {
            font-size: 0.9rem;
            color: #28a745;
            font-weight: 600;
        }
        
        .btn-action {
            padding: 0.25rem 0.5rem;
            font-size: 0.875rem;
            border-radius: 0.375rem;
        }
    </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">Insurance 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">Products & Quotes</li>
                                        <li class="breadcrumb-item active">Insurance Products</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Stats Overview -->
                    <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-grow-1">
                                            <p class="text-uppercase fw-medium mb-0" style="opacity: 0.8;">Total Products</p>
                                            <h4 class="mb-0"><?php echo number_format($total_records); ?></h4>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded-circle bg-white bg-opacity-25">
                                                <span class="avatar-title rounded-circle">
                                                    <i class="ri-shield-check-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card bg-success text-white">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium mb-0" style="opacity: 0.8;">Active Products</p>
                                            <h4 class="mb-0">
                                                <?php 
                                                $active_count = 0;
                                                mysqli_data_seek($result, 0);
                                                while($row = $result->fetch_assoc()) {
                                                    if($row['is_active']) $active_count++;
                                                }
                                                echo number_format($active_count);
                                                mysqli_data_seek($result, 0);
                                                ?>
                                            </h4>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded-circle bg-white bg-opacity-25">
                                                <span class="avatar-title rounded-circle">
                                                    <i class="ri-check-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card bg-info text-white">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium mb-0" style="opacity: 0.8;">Categories</p>
                                            <h4 class="mb-0"><?php echo $categories_result->num_rows; ?></h4>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded-circle bg-white bg-opacity-25">
                                                <span class="avatar-title rounded-circle">
                                                    <i class="ri-grid-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card bg-warning text-white">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium mb-0" style="opacity: 0.8;">Insurance Companies</p>
                                            <h4 class="mb-0"><?php echo $companies_result->num_rows; ?></h4>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded-circle bg-white bg-opacity-25">
                                                <span class="avatar-title rounded-circle">
                                                    <i class="ri-building-line fs-4"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters and Actions -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="card filter-card">
                                <div class="card-body">
                                    <form method="GET" class="row g-3 align-items-end">
                                        <div class="col-md-3">
                                            <label class="form-label">Search Products</label>
                                            <input type="text" class="form-control" name="search" 
                                                   placeholder="Product name, code..." 
                                                   value="<?php echo htmlspecialchars($search); ?>">
                                        </div>
                                        
                                        <div class="col-md-2">
                                            <label class="form-label">Category</label>
                                            <select class="form-select" name="category">
                                                <option value="">All Categories</option>
                                                <?php 
                                                mysqli_data_seek($categories_result, 0);
                                                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>
                                        </div>
                                        
                                        <div class="col-md-2">
                                            <label class="form-label">Company</label>
                                            <select class="form-select" name="company">
                                                <option value="">All Companies</option>
                                                <?php 
                                                mysqli_data_seek($companies_result, 0);
                                                while($comp = $companies_result->fetch_assoc()): ?>
                                                    <option value="<?php echo $comp['company_id']; ?>" 
                                                            <?php echo ($company_filter == $comp['company_id']) ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($comp['company_name']); ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                        
                                        <div class="col-md-2">
                                            <label class="form-label">Status</label>
                                            <select class="form-select" name="status">
                                                <option value="">All Status</option>
                                                <option value="active" <?php echo ($status_filter === 'active') ? 'selected' : ''; ?>>Active</option>
                                                <option value="inactive" <?php echo ($status_filter === 'inactive') ? 'selected' : ''; ?>>Inactive</option>
                                            </select>
                                        </div>
                                        
                                        <div class="col-md-3">
                                            <div class="d-flex gap-2">
                                                <button type="submit" class="btn btn-primary">
                                                    <i class="ri-search-line me-1"></i> Search
                                                </button>
                                                <a href="list_products.php" class="btn btn-secondary">
                                                    <i class="ri-refresh-line me-1"></i> Reset
                                                </a>
                                               
                                                <a href="add_product.php" class="btn btn-success">
                                                    <i class="ri-add-line me-1"></i> Add Product
                                                </a>
                                               
                                            </div>
                                        </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-lg-6 col-md-6 mb-4">
                                <div class="card product-card h-100">
                                    <div class="card-body">
                                        <!-- Product Header -->
                                        <div class="d-flex justify-content-between align-items-start mb-3">
                                            <div>
                                                <h5 class="card-title mb-1"><?php echo htmlspecialchars($product['product_name']); ?></h5>
                                                <p class="text-muted small mb-0"><?php echo htmlspecialchars($product['product_code']); ?></p>
                                            </div>
                                            <span class="badge <?php echo $product['is_active'] ? 'bg-success' : 'bg-secondary'; ?>">
                                                <?php echo $product['is_active'] ? 'Active' : 'Inactive'; ?>
                                            </span>
                                        </div>
                                        
                                        <!-- Product Details -->
                                        <div class="mb-3">
                                            <div class="row g-2 mb-2">
                                                <div class="col-6">
                                                    <span class="coverage-badge bg-primary text-white">
                                                        <?php echo ucwords(str_replace('_', ' ', $product['coverage_type'])); ?>
                                                    </span>
                                                </div>
                                                <div class="col-6">
                                                    <span class="coverage-badge bg-info text-white">
                                                        <?php echo ucwords(str_replace('_', ' ', $product['product_type'])); ?>
                                                    </span>
                                                </div>
                                            </div>
                                            
                                            <p class="text-muted small mb-2">
                                                <strong>Category:</strong> <?php echo htmlspecialchars($product['category_name']); ?>
                                            </p>
                                            <p class="text-muted small mb-2">
                                                <strong>Company:</strong> <?php echo htmlspecialchars($product['company_name']); ?>
                                            </p>
                                        </div>
                                        
                                        <!-- Premium and Coverage -->
                                        <div class="mb-3">
                                            <?php if($product['base_premium']): ?>
                                            <div class="premium-range">
                                                Base Premium: KSH <?php echo number_format($product['base_premium'], 2); ?>
                                            </div>
                                            <?php endif; ?>
                                            
                                            <?php if($product['sum_assured_min'] && $product['sum_assured_max']): ?>
                                            <div class="text-muted small">
                                                Coverage: KSH <?php echo number_format($product['sum_assured_min']); ?> - 
                                                KSH <?php echo number_format($product['sum_assured_max']); ?>
                                            </div>
                                            <?php endif; ?>
                                            
                                            <div class="text-muted small">
                                                Age Range: <?php echo $product['age_range_min']; ?> - <?php echo $product['age_range_max']; ?> years
                                            </div>
                                            
                                            <div class="text-success small">
                                                Commission: <?php echo $product['commission_rate']; ?>%
                                            </div>
                                        </div>
                                        
                                        <!-- Performance Stats -->
                                        <div class="row text-center mb-3">
                                            <div class="col-6">
                                                <div class="border-end">
                                                    <h5 class="mb-0"><?php echo number_format($product['active_policies']); ?></h5>
                                                    <p class="text-muted small mb-0">Active Policies</p>
                                                </div>
                                            </div>
                                            <div class="col-6">
                                                <h5 class="mb-0">KSH <?php echo number_format($product['total_premium_volume']); ?></h5>
                                                <p class="text-muted small mb-0">Premium Volume</p>
                                            </div>
                                        </div>
                                        
                                        <!-- Description -->
                                        <?php if($product['description']): ?>
                                        <p class="text-muted small mb-3">
                                            <?php echo substr(htmlspecialchars($product['description']), 0, 100); ?>
                                            <?php if(strlen($product['description']) > 100) echo '...'; ?>
                                        </p>
                                        <?php endif; ?>
                                    </div>
                                    
                                    <!-- Card Actions -->
                                    <div class="card-footer bg-transparent border-top-0">
                                        <div class="d-flex gap-2 justify-content-between">
                                            <a href="view_product.php?id=<?php echo $product['product_id']; ?>" 
                                               class="btn btn-outline-primary btn-action">
                                                <i class="ri-eye-line me-1"></i> View
                                            </a>
                                            
                                           
                                            <a href="edit_product.php?id=<?php echo $product['product_id']; ?>" 
                                               class="btn btn-outline-secondary btn-action">
                                                <i class="ri-edit-line me-1"></i> Edit
                                            </a>
                                            
                                            
                                            <a href="quotes/new_quote.php?product_id=<?php echo $product['product_id']; ?>" 
                                               class="btn btn-outline-success btn-action">
                                                <i class="ri-calculator-line me-1"></i> Quote
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <?php endwhile; ?>
                        <?php else: ?>
                            <div class="col-12">
                                <div class="card">
                                    <div class="card-body text-center py-5">
                                        <div class="mb-3">
                                            <i class="ri-shield-check-line display-4 text-muted"></i>
                                        </div>
                                        <h5 class="mb-3">No Products Found</h5>
                                        <p class="text-muted mb-3">No insurance products match your current filters.</p>
                                        <?php if(hasPermission('products_create')): ?>
                                        <a href="add_product.php" class="btn btn-primary">
                                            <i class="ri-add-line me-1"></i> Add First Product
                                        </a>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            </div>
                        <?php endif; ?>
                    </div>

                    <!-- Pagination -->
                    <?php if($total_pages > 1): ?>
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <div class="d-flex justify-content-between align-items-center">
                                        <div>
                                            <p class="text-muted mb-0">
                                                Showing <?php echo $offset + 1; ?> to 
                                                <?php echo min($offset + $records_per_page, $total_records); ?> 
                                                of <?php echo $total_records; ?> products
                                            </p>
                                        </div>
                                        
                                        <nav aria-label="Page navigation">
                                            <ul class="pagination mb-0">
                                                <?php if($page > 1): ?>
                                                <li class="page-item">
                                                    <a class="page-link" href="?page=1<?php echo $search ? '&search='.urlencode($search) : ''; ?><?php echo $category_filter ? '&category='.$category_filter : ''; ?><?php echo $company_filter ? '&company='.$company_filter : ''; ?><?php echo $status_filter ? '&status='.$status_filter : ''; ?>">
                                                        First
                                                    </a>
                                                </li>
                                                <li class="page-item">
                                                    <a class="page-link" href="?page=<?php echo $page-1; ?><?php echo $search ? '&search='.urlencode($search) : ''; ?><?php echo $category_filter ? '&category='.$category_filter : ''; ?><?php echo $company_filter ? '&company='.$company_filter : ''; ?><?php echo $status_filter ? '&status='.$status_filter : ''; ?>">
                                                        Previous
                                                    </a>
                                                </li>
                                                <?php endif; ?>
                                                
                                                <?php 
                                                $start_page = max(1, $page - 2);
                                                $end_page = min($total_pages, $page + 2);
                                                
                                                for($i = $start_page; $i <= $end_page; $i++): 
                                                ?>
                                                <li class="page-item <?php echo ($i == $page) ? 'active' : ''; ?>">
                                                    <a class="page-link" href="?page=<?php echo $i; ?><?php echo $search ? '&search='.urlencode($search) : ''; ?><?php echo $category_filter ? '&category='.$category_filter : ''; ?><?php echo $company_filter ? '&company='.$company_filter : ''; ?><?php echo $status_filter ? '&status='.$status_filter : ''; ?>">
                                                        <?php echo $i; ?>
                                                    </a>
                                                </li>
                                                <?php endfor; ?>
                                                
                                                <?php if($page < $total_pages): ?>
                                                <li class="page-item">
                                                    <a class="page-link" href="?page=<?php echo $page+1; ?><?php echo $search ? '&search='.urlencode($search) : ''; ?><?php echo $category_filter ? '&category='.$category_filter : ''; ?><?php echo $company_filter ? '&company='.$company_filter : ''; ?><?php echo $status_filter ? '&status='.$status_filter : ''; ?>">
                                                        Next
                                                    </a>
                                                </li>
                                                <li class="page-item">
                                                    <a class="page-link" href="?page=<?php echo $total_pages; ?><?php echo $search ? '&search='.urlencode($search) : ''; ?><?php echo $category_filter ? '&category='.$category_filter : ''; ?><?php echo $company_filter ? '&company='.$company_filter : ''; ?><?php echo $status_filter ? '&status='.$status_filter : ''; ?>">
                                                        Last
                                                    </a>
                                                </li>
                                                <?php endif; ?>
                                            </ul>
                                        </nav>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>

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

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- DataTables JS -->
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/dataTables.bootstrap5.min.js"></script>
    
    <!-- Select2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize Select2 for dropdowns
            $('.form-select').select2({
                theme: 'bootstrap-5',
                width: '100%'
            });
            
            // Animate cards on load
            $('.product-card').each(function(index) {
                $(this).css('opacity', '0').delay(index * 100).animate({
                    opacity: 1
                }, 500);
            });
        });
    </script>
    
    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>