{% extends "base.html" %}

{% block title %}Build Database - Daily Mirror{% endblock %}

{% block extra_css %}
<link rel="stylesheet" href="{{ url_for('static', filename='daily_mirror_tune.css') }}">
{% endblock %}

{% block content %}
<div class="container-fluid">
    <!-- Page Header -->
    <div class="row mb-4">
        <div class="col-12">
            <div class="d-flex justify-content-between align-items-center">
                <div>
                    <h1 class="h3 mb-0">🔨 Build Database</h1>
                    <p class="text-muted">Upload Excel files to populate Daily Mirror database tables</p>
                </div>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-lg-6 mb-4">
            <!-- Card 1: Upload Excel File -->
            <div class="card h-100">
                <div class="card-header bg-primary text-white">
                    <h5 class="card-title mb-0">
                        <i class="fas fa-upload"></i> Upload Excel File
                    </h5>
                </div>
                <div class="card-body">
                    <form method="POST" enctype="multipart/form-data" id="uploadForm">
                        <!-- Table Selection -->
                        <div class="form-group mb-4">
                            <label for="target_table" class="form-label">
                                <strong>Select Target Table:</strong>
                            </label>
                            <select class="form-control" name="target_table" id="target_table" required>
                                <option value="">-- Choose a table --</option>
                                {% for table in available_tables %}
                                <option value="{{ table.name }}" data-description="{{ table.description }}">
                                    {{ table.display }}
                                </option>
                                {% endfor %}
                            </select>
                            <small id="tableDescription" class="form-text text-muted mt-2">
                                Select a table to see its description.
                            </small>
                        </div>
                        
                        <!-- File Upload -->
                        <div class="form-group mb-4">
                            <label for="excel_file" class="form-label">
                                <strong>Select Excel File:</strong>
                            </label>
                            <input type="file" class="form-control" name="excel_file" id="excel_file" 
                                   accept=".xlsx,.xls" required>
                            <small class="form-text text-muted">
                                Accepted formats: .xlsx, .xls (Maximum file size: 10MB)
                            </small>
                        </div>
                        
                        <!-- Upload Button -->
                        <div class="text-center">
                            <button type="button" class="btn btn-primary btn-lg" id="uploadBtn">
                                <i class="fas fa-cloud-upload-alt"></i> Upload and Process File
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        </div>
        
        <div class="col-lg-6 mb-4">
            <!-- Card 2: Excel File Format Instructions -->
            <div class="card h-100">
                <div class="card-header bg-info text-white">
                    <h5 class="card-title mb-0">
                        <i class="fas fa-info-circle"></i> Excel File Format Instructions
                    </h5>
                </div>
                <div class="card-body">
                    <div class="accordion" id="formatAccordion">
                        <!-- Production Data Format -->
                        <div class="accordion-item">
                            <h2 class="accordion-header">
                                <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" 
                                        data-bs-target="#productionCollapse" aria-expanded="false">
                                    🏭 Production Data Format
                                </button>
                            </h2>
                            <div id="productionCollapse" class="accordion-collapse collapse" data-bs-parent="#formatAccordion">
                                <div class="accordion-body">
                                    <p><strong>Expected columns for Production Data:</strong></p>
                                    <div class="row">
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Production Order ID</code> <span class="text-muted">Unique identifier</span></li>
                                                <li><code>Customer Code</code> <span class="text-muted">Customer code</span></li>
                                                <li><code>Customer Name</code> <span class="text-muted">Customer name</span></li>
                                                <li><code>Article Code</code> <span class="text-muted">Article code</span></li>
                                            </ul>
                                        </div>
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Article Description</code> <span class="text-muted">Description</span></li>
                                                <li><code>Quantity</code> <span class="text-muted">To produce</span></li>
                                                <li><code>Production Date</code> <span class="text-muted">Date</span></li>
                                                <li><code>Status</code> <span class="text-muted">Production status</span></li>
                                            </ul>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Orders Data Format -->
                        <div class="accordion-item">
                            <h2 class="accordion-header">
                                <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" 
                                        data-bs-target="#ordersCollapse" aria-expanded="false">
                                    🛒 Orders Data Format
                                </button>
                            </h2>
                            <div id="ordersCollapse" class="accordion-collapse collapse" data-bs-parent="#formatAccordion">
                                <div class="accordion-body">
                                    <p><strong>Expected columns for Orders Data:</strong></p>
                                    <div class="row">
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Order ID</code> <span class="text-muted">Unique identifier</span></li>
                                                <li><code>Customer Code</code> <span class="text-muted">Customer code</span></li>
                                                <li><code>Customer Name</code> <span class="text-muted">Customer name</span></li>
                                                <li><code>Article Code</code> <span class="text-muted">Article code</span></li>
                                            </ul>
                                        </div>
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Article Description</code> <span class="text-muted">Description</span></li>
                                                <li><code>Quantity Ordered</code> <span class="text-muted">Ordered</span></li>
                                                <li><code>Order Date</code> <span class="text-muted">Date</span></li>
                                                <li><code>Status</code> <span class="text-muted">Order status</span></li>
                                            </ul>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Delivery Data Format -->
                        <div class="accordion-item">
                            <h2 class="accordion-header">
                                <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" 
                                        data-bs-target="#deliveryCollapse" aria-expanded="false">
                                    🚚 Delivery Data Format (Articole livrate)
                                </button>
                            </h2>
                            <div id="deliveryCollapse" class="accordion-collapse collapse" data-bs-parent="#formatAccordion">
                                <div class="accordion-body">
                                    <p><strong>Expected columns for Delivery Data:</strong></p>
                                    <div class="row">
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Shipment ID</code> <span class="text-muted">Unique shipment identifier</span></li>
                                                <li><code>Order ID</code> <span class="text-muted">Related order</span></li>
                                                <li><code>Customer</code> <span class="text-muted">Customer info</span></li>
                                                <li><code>Article</code> <span class="text-muted">Code/description</span></li>
                                            </ul>
                                        </div>
                                        <div class="col-md-6">
                                            <ul class="list-unstyled mb-0">
                                                <li><code>Quantity Delivered</code> <span class="text-muted">Delivered quantity</span></li>
                                                <li><code>Delivery Date</code> <span class="text-muted">Date</span></li>
                                                <li><code>Status</code> <span class="text-muted">Delivery status</span></li>
                                            </ul>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Upload Result Modal (Better Solution) -->
<div class="modal fade" id="uploadResultModal" tabindex="-1" aria-labelledby="uploadResultModalLabel" aria-hidden="true" data-bs-backdrop="true" data-bs-keyboard="true">
    <div class="modal-dialog modal-dialog-centered">
        <div class="modal-content">
            <div class="modal-header" id="modalHeader">
                <h5 class="modal-title" id="uploadResultModalLabel">
                    <i class="fas fa-check-circle"></i> Upload Result
                </h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close" id="modalCloseBtn"></button>
            </div>
            <div class="modal-body">
                <div id="uploadResultContent" class="text-center py-3">
                    <!-- Result content will be inserted here -->
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-primary" data-bs-dismiss="modal" id="modalOkBtn">OK</button>
            </div>
        </div>
    </div>
</div>

<style>
/* Additional modal fixes specific to this page */
#uploadResultModal {
    z-index: 10000 !important;
    pointer-events: auto !important;
}

#uploadResultModal .modal-dialog {
    pointer-events: auto !important;
    z-index: 10001 !important;
}

#uploadResultModal .modal-content {
    pointer-events: auto !important;
    background-color: white !important;
}

#uploadResultModal .modal-backdrop {
    z-index: 9998 !important;
}

#uploadResultModal .btn-close,
#uploadResultModal .modal-footer button {
    pointer-events: auto !important;
    cursor: pointer !important;
    opacity: 1 !important;
}

/* Ensure modal can be dismissed */
.modal-backdrop.show {
    pointer-events: auto !important;
}

/* Dark mode modal fixes */
body.dark-mode #uploadResultModal .modal-content {
    background-color: #2d3748 !important;
}

.accordion-button:not(.collapsed) {
    background-color: #e7f3ff;
    color: #0066cc;
}

.form-control:focus {
    border-color: #007bff;
    box-shadow: 0 0 0 0.2rem rgba(0, 123, 255, 0.25);
}

.btn-primary {
    transition: all 0.3s ease;
}

.btn-primary:hover {
    transform: translateY(-2px);
    box-shadow: 0 4px 8px rgba(0, 123, 255, 0.3);
}

/* Result stats styling */
.upload-stats {
    display: flex;
    justify-content: space-around;
    flex-wrap: wrap;
    margin-top: 20px;
}

.stat-box {
    text-align: center;
    padding: 15px;
    min-width: 100px;
    margin: 5px;
    border-radius: 8px;
}

.stat-box.success {
    background-color: #d4edda;
    border: 1px solid #c3e6cb;
}

.stat-box.warning {
    background-color: #fff3cd;
    border: 1px solid #ffeeba;
}

.stat-box.error {
    background-color: #f8d7da;
    border: 1px solid #f5c6cb;
}

.stat-value {
    font-size: 2rem;
    font-weight: bold;
    display: block;
}

.stat-label {
    font-size: 0.9rem;
    color: #666;
}

/* Reduce font size for Excel Format Instructions card rows */
.col-lg-6:nth-child(2) .card-body {
    font-size: 0.95rem;
}

/* Make accordion button labels smaller */
.accordion-button {
    font-size: 1rem;
    padding-top: 0.4rem;
    padding-bottom: 0.4rem;
}

/* Override h2 size in accordion headers */
.accordion-header {
    font-size: 1rem;
}

.accordion-header h2 {
    font-size: 1rem;
    margin: 0;
}

/* Modal summary list styling */
#uploadResultContent ul {
    list-style-type: none;
    padding-left: 0;
    margin: 10px auto;
    max-width: 400px;
}

#uploadResultContent ul li {
    padding: 5px 0;
    font-size: 1rem;
}

#uploadResultContent ul li::before {
    content: '✓ ';
    color: #28a745;
    font-weight: bold;
    margin-right: 5px;
}

/* Make "Expected columns" text smaller in accordion bodies */
.accordion-body p {
    font-size: 0.9rem;
}

.accordion-body strong {
    font-size: 0.9rem;
}

/* Dark mode styles */
body.dark-mode .card {
    background-color: #2d3748;
    border-color: #4a5568;
    color: #e2e8f0;
    box-shadow: 0 4px 6px rgba(255, 255, 255, 0.1);
}

body.dark-mode .card-header {
    background-color: #4a5568;
    color: #e2e8f0;
    border-bottom: 1px solid rgba(226, 232, 240, 0.2);
}

body.dark-mode .form-control {
    background-color: #4a5568;
    border-color: #6b7280;
    color: #e2e8f0;
}

body.dark-mode .form-control:focus {
    background-color: #4a5568;
    border-color: #007bff;
    color: #e2e8f0;
    box-shadow: 0 0 0 0.2rem rgba(0, 123, 255, 0.25);
}

body.dark-mode .accordion-button {
    background-color: #374151;
    color: #e2e8f0;
    border-color: #6b7280;
}

body.dark-mode .accordion-button:not(.collapsed) {
    background-color: #1e3a8a;
    color: #bfdbfe;
}

body.dark-mode .accordion-body {
    background-color: #374151;
    color: #e2e8f0;
}

body.dark-mode code {
    background-color: #374151;
    color: #e2e8f0;
    border: 1px solid #6b7280;
}

body.dark-mode .modal-content {
    background-color: #2d3748;
    color: #e2e8f0;
}

body.dark-mode .modal-header {
    border-bottom-color: #4a5568;
}

body.dark-mode .modal-footer {
    border-top-color: #4a5568;
}

body.dark-mode .stat-box.success {
    background-color: #1e4620;
    border-color: #2d5a2e;
    color: #a3d9a5;
}

body.dark-mode .stat-box.warning {
    background-color: #5a4a1e;
    border-color: #6b5a2d;
    color: #f4d88f;
}

body.dark-mode .stat-box.error {
    background-color: #5a1e1e;
    border-color: #6b2d2d;
    color: #f8a3a8;
}

body.dark-mode .stat-label {
    color: #a0aec0;
}

body.dark-mode .text-muted {
    color: #a0aec0 !important;
}
</style>

<script>
document.addEventListener('DOMContentLoaded', function() {
    // Initialize theme on page load
    const savedTheme = localStorage.getItem('theme');
    const body = document.body;
    
    if (savedTheme === 'dark') {
        body.classList.add('dark-mode');
    } else {
        body.classList.remove('dark-mode');
    }

    const tableSelect = document.getElementById('target_table');
    const tableDescription = document.getElementById('tableDescription');
    const uploadBtn = document.getElementById('uploadBtn');
    const uploadForm = document.getElementById('uploadForm');
    const fileInput = document.getElementById('excel_file');

    // Update table description when selection changes
    tableSelect.addEventListener('change', function() {
        const selectedOption = this.options[this.selectedIndex];
        if (selectedOption.value) {
            const description = selectedOption.getAttribute('data-description');
            tableDescription.innerHTML = `<strong>Selected:</strong> ${description}`;
            tableDescription.className = 'form-text text-info mt-2';
        } else {
            tableDescription.innerHTML = 'Select a table to see its description.';
            tableDescription.className = 'form-text text-muted mt-2';
        }
    });

    // File input change handler to show file info
    fileInput.addEventListener('change', function(e) {
        const file = e.target.files[0];
        if (file) {
            const fileName = file.name;
            const fileSize = (file.size / 1024 / 1024).toFixed(2);
            console.log(`Selected file: ${fileName} (${fileSize} MB)`);
        }
    });

    // Upload button click handler (AJAX submission)
    uploadBtn.addEventListener('click', function(e) {
        e.preventDefault();
        
        // Validate file selection
        if (!fileInput.files.length) {
            alert('Please select an Excel file to upload.');
            return;
        }
        
        // Validate table selection
        if (!tableSelect.value) {
            alert('Please select a target table.');
            return;
        }
        
        // Check file size (10MB limit)
        const file = fileInput.files[0];
        if (file.size > 10 * 1024 * 1024) {
            alert('File size must be less than 10MB.');
            return;
        }
        
        // Prepare form data
        const formData = new FormData(uploadForm);
        
        // Show loading state
        uploadBtn.innerHTML = '<i class="fas fa-spinner fa-spin"></i> Processing...';
        uploadBtn.disabled = true;
        
        // Submit via AJAX
        fetch('/daily_mirror/build_database', {
            method: 'POST',
            body: formData,
            headers: {
                'X-Requested-With': 'XMLHttpRequest'
            }
        })
        .then(response => {
            if (!response.ok) {
                return response.json().then(err => Promise.reject(err));
            }
            return response.json();
        })
        .then(result => {
            // Reset button
            uploadBtn.innerHTML = '<i class="fas fa-cloud-upload-alt"></i> Upload and Process File';
            uploadBtn.disabled = false;
            
            // Show result in modal
            showUploadResult(result);
            
            // Reset form
            uploadForm.reset();
            tableDescription.innerHTML = 'Select a table to see its description.';
            tableDescription.className = 'form-text text-muted mt-2';
        })
        .catch(error => {
            // Reset button
            uploadBtn.innerHTML = '<i class="fas fa-cloud-upload-alt"></i> Upload and Process File';
            uploadBtn.disabled = false;
            
            // Show error in modal
            showUploadError(error);
        });
    });

    function showUploadResult(result) {
        const modal = new bootstrap.Modal(document.getElementById('uploadResultModal'));
        const modalHeader = document.getElementById('modalHeader');
        const modalTitle = document.getElementById('uploadResultModalLabel');
        const content = document.getElementById('uploadResultContent');
        
        // Determine overall status
        const hasErrors = result.error_count && result.error_count > 0;
        const hasSuccess = result.created_rows > 0 || result.updated_rows > 0;
        
        // Update modal header color
        if (hasErrors && !hasSuccess) {
            modalHeader.className = 'modal-header bg-danger text-white';
            modalTitle.innerHTML = '<i class="fas fa-times-circle"></i> Upload Failed';
        } else if (hasErrors && hasSuccess) {
            modalHeader.className = 'modal-header bg-warning text-dark';
            modalTitle.innerHTML = '<i class="fas fa-exclamation-triangle"></i> Upload Completed with Warnings';
        } else {
            modalHeader.className = 'modal-header bg-success text-white';
            modalTitle.innerHTML = '<i class="fas fa-check-circle"></i> Upload Successful';
        }
        
        // Build result content with stats
        let html = '<div class="upload-stats">';
        
        // Total rows processed from Excel
        html += `
            <div class="stat-box ${hasErrors ? 'warning' : 'success'}">
                <span class="stat-value">${result.total_rows || 0}</span>
                <span class="stat-label">Rows Processed</span>
            </div>
        `;
        
        // Created rows (new in database)
        if (result.created_rows > 0) {
            html += `
                <div class="stat-box success">
                    <span class="stat-value">${result.created_rows}</span>
                    <span class="stat-label">New Rows Created</span>
                </div>
            `;
        }
        
        // Updated rows (existing in database)
        if (result.updated_rows > 0) {
            html += `
                <div class="stat-box success">
                    <span class="stat-value">${result.updated_rows}</span>
                    <span class="stat-label">Rows Updated</span>
                </div>
            `;
        }
        
        // Errors
        if (hasErrors) {
            html += `
                <div class="stat-box error">
                    <span class="stat-value">${result.error_count}</span>
                    <span class="stat-label">Errors</span>
                </div>
            `;
        }
        
        html += '</div>';
        
        // Add detailed summary message
        const successCount = (result.created_rows || 0) + (result.updated_rows || 0);
        if (successCount > 0) {
            let msg = `<p class="mt-3 mb-0"><strong>Successfully processed ${result.total_rows} rows from Excel:</strong></p>`;
            msg += '<ul class="text-start">';
            if (result.created_rows > 0) {
                msg += `<li>${result.created_rows} new ${result.created_rows === 1 ? 'record' : 'records'} created in database</li>`;
            }
            if (result.updated_rows > 0) {
                msg += `<li>${result.updated_rows} existing ${result.updated_rows === 1 ? 'record' : 'records'} updated</li>`;
            }
            msg += '</ul>';
            html += msg;
        }
        if (hasErrors) {
            html += `<p class="text-danger mb-0"><strong>⚠️ ${result.error_count} ${result.error_count === 1 ? 'row' : 'rows'} could not be processed due to errors.</strong></p>`;
        }
        
        // Add auto-close countdown for successful uploads without errors
        if (!hasErrors && successCount > 0) {
            html += `<p class="text-muted mt-2 mb-0" id="autoCloseCountdown"><small>This window will close automatically in <span id="countdown">3</span> seconds...</small></p>`;
        }
        
        content.innerHTML = html;
        modal.show();
        
        // Get modal element
        const modalElement = document.getElementById('uploadResultModal');
        
        // Add explicit close handlers
        const okBtn = document.getElementById('modalOkBtn');
        const closeBtn = document.getElementById('modalCloseBtn');
        
        if (okBtn) {
            okBtn.onclick = function() {
                modal.hide();
                // Also trigger Bootstrap's native close
                modalElement.classList.remove('show');
                document.querySelector('.modal-backdrop')?.remove();
                document.body.classList.remove('modal-open');
                document.body.style.overflow = '';
                document.body.style.paddingRight = '';
            };
        }
        
        if (closeBtn) {
            closeBtn.onclick = function() {
                modal.hide();
                // Also trigger Bootstrap's native close
                modalElement.classList.remove('show');
                document.querySelector('.modal-backdrop')?.remove();
                document.body.classList.remove('modal-open');
                document.body.style.overflow = '';
                document.body.style.paddingRight = '';
            };
        }
        
        // Auto-close after 3 seconds for successful uploads without errors
        if (!hasErrors && successCount > 0) {
            let countdown = 3;
            const countdownInterval = setInterval(function() {
                countdown--;
                const countdownSpan = document.getElementById('countdown');
                if (countdownSpan) {
                    countdownSpan.textContent = countdown;
                }
                if (countdown <= 0) {
                    clearInterval(countdownInterval);
                }
            }, 1000);
            
            setTimeout(function() {
                clearInterval(countdownInterval);
                modal.hide();
                modalElement.classList.remove('show');
                document.querySelector('.modal-backdrop')?.remove();
                document.body.classList.remove('modal-open');
                document.body.style.overflow = '';
                document.body.style.paddingRight = '';
            }, 3000);
        }
    }

    function showUploadError(error) {
        const modal = new bootstrap.Modal(document.getElementById('uploadResultModal'));
        const modalHeader = document.getElementById('modalHeader');
        const modalTitle = document.getElementById('uploadResultModalLabel');
        const content = document.getElementById('uploadResultContent');
        
        // Update modal header
        modalHeader.className = 'modal-header bg-danger text-white';
        modalTitle.innerHTML = '<i class="fas fa-times-circle"></i> Upload Error';
        
        // Show error message
        const errorMsg = error.error || error.message || 'An unexpected error occurred during upload.';
        content.innerHTML = `
            <div class="alert alert-danger mb-0">
                <i class="fas fa-exclamation-triangle"></i> 
                <strong>Error:</strong> ${errorMsg}
            </div>
        `;
        
        modal.show();
        
        // Get modal element
        const modalElement = document.getElementById('uploadResultModal');
        
        // Add explicit close handlers
        const okBtn = document.getElementById('modalOkBtn');
        const closeBtn = document.getElementById('modalCloseBtn');
        
        if (okBtn) {
            okBtn.onclick = function() {
                modal.hide();
                // Also trigger Bootstrap's native close
                modalElement.classList.remove('show');
                document.querySelector('.modal-backdrop')?.remove();
                document.body.classList.remove('modal-open');
                document.body.style.overflow = '';
                document.body.style.paddingRight = '';
            };
        }
        
        if (closeBtn) {
            closeBtn.onclick = function() {
                modal.hide();
                // Also trigger Bootstrap's native close
                modalElement.classList.remove('show');
                document.querySelector('.modal-backdrop')?.remove();
                document.body.classList.remove('modal-open');
                document.body.style.overflow = '';
                document.body.style.paddingRight = '';
            };
        }
    }
});
</script>
{% endblock %}