from flask import Flask, render_template, request, redirect, url_for
import psycopg2
import json

app = Flask(__name__)

# Database connection parameters
DB_HOST = "aws-0-ap-south-1.pooler.supabase.com"
DB_PORT = "6543"
DB_NAME = "postgres"
DB_USER = "postgres.lvwgmrdzbsgpwxgmjmcf"
DB_PASSWORD = "mJ37Ni[1a]Rs!"

def get_db_connection():
    return psycopg2.connect(
        host=DB_HOST,
        port=DB_PORT,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )

@app.route('/')
def home():
    return redirect(url_for('index'))

@app.route('/index', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        pincode = request.form['pincode']
        count = int(request.form['count'])
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO pincode_data (pincode, count)
            VALUES (%s, %s)
            ON CONFLICT (pincode) DO UPDATE SET count = EXCLUDED.count;
        ''', (pincode, count))
        
        conn.commit()
        cursor.close()
        conn.close()
        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT pincode, count, latitude, longitude, "Live"
        FROM pincode_data
    ''')
    
    data = cursor.fetchall()
    cursor.close()
    conn.close()

    locations = []
    for pincode, count, latitude, longitude, live in data:
        if latitude is not None and longitude is not None:
            locations.append({
                'pincode': pincode,
                'count': count,
                'type': 'live' if live == 'YES' else 'dead',
                'coordinates': [latitude, longitude]
            })

    print("Locations to be passed to the template:", locations)  # Debug output

    return render_template('index.html', locations=json.dumps(locations))

if __name__ == '__main__':
    app.run(debug=True)






this is my app.py 





-------------------------




<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Visited Locations</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
            color: #333;
            text-align: center;
        }
        h1 {
            color: #0056b3;
        }
        .container {
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
        }
        form {
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        label {
            display: block;
            margin: 10px 0 5px;
        }
        input[type="text"], input[type="number"] {
            width: 100%;
            padding: 8px;
            margin: 5px 0 10px;
            border: 1px solid #ccc;
            border-radius: 4px;
        }
        button {
            background-color: #007bff;
            color: white;
            border: none;
            padding: 10px 15px;
            border-radius: 4px;
            cursor: pointer;
        }
        button:hover {
            background-color: #0056b3;
        }
        .map-container {
            width: 100%;
            height: 500px;
            position: relative;
            border: 2px solid #ddd;
            margin: 20px auto;
            background-color: #fff;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        #map {
            width: 100%;
            height: 100%;
        }
        .radio-group {
            display: flex;
            justify-content: center;
            margin: 20px 0;
        }
        .radio-group input[type="radio"] {
            display: none;
        }
        .radio-group label {
            margin: 0 10px;
            padding: 10px 20px;
            border: 2px solid #007bff;
            border-radius: 20px;
            cursor: pointer;
            transition: all 0.3s ease;
        }
        .radio-group input[type="radio"]:checked + label {
            background-color: #007bff;
            color: white;
        }
    </style>
    <link rel="stylesheet" href="https://unpkg.com/leaflet@1.7.1/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet@1.7.1/dist/leaflet.js"></script>
    <script>
        function initMap() {
            var map = L.map('map').setView([19.0760, 72.8777], 12);  // Center the map

            L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
                attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
            }).addTo(map);

            var locations = {{ locations | tojson | safe }}; // Pass locations data from Flask

            console.log("Fetched locations from server:", locations); // Log locations data for debugging

            function addMarkers(filter) {
                map.eachLayer(function(layer) {
                    if (!!layer.toGeoJSON) {
                        map.removeLayer(layer);
                    }
                });

                locations.forEach(function(location) {
                    if (filter === 'both' || location.type === filter) {
                        var color = location.type === 'live' ? 'green' : 'red';
                        if (location.coordinates[0] !== null && location.coordinates[1] !== null) {
                            L.marker(location.coordinates, {
                                icon: L.icon({
                                    iconUrl: 'http://maps.google.com/mapfiles/ms/icons/' + color + '-dot.png',
                                    iconSize: [32, 32]
                                })
                            }).addTo(map).bindPopup('Pincode: ' + location.pincode + '<br>Visits: ' + location.count);
                        }
                    }
                });
            }

            // Radio button event listeners
            document.querySelectorAll('.radio-group input[type="radio"]').forEach(function(input) {
                input.addEventListener('change', function() {
                    addMarkers(this.value);
                });
            });

            addMarkers('both'); // Initial load with both markers
        }
    </script>
</head>
<body onload="initMap()">
    <div class="container">
        <h1>Visited Locations</h1>
        <form method="post" action="{{ url_for('index') }}">
            <label for="pincode">Enter Pincode:</label>
            <input type="text" id="pincode" name="pincode" required>
            <label for="count">Enter Count:</label>
            <input type="number" id="count" name="count" required>
            <button type="submit">Update</button>
        </form>
        <div class="radio-group">
            <input type="radio" id="show-both" name="filter" value="both" checked>
            <label for="show-both">Show Both</label>
            <input type="radio" id="show-live" name="filter" value="live">
            <label for="show-live">Live Locations</label>
            <input type="radio" id="show-dead" name="filter" value="dead">
            <label for="show-dead">Dead Locations</label>
        </div>
        <div class="map-container">
            <div id="map"></div>
        </div>
    </div>
</body>
</html>



this is index.html 


------------------------------------------



<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Welcome to Dava Ninja</title>
    <style>
        body {
            background-color: #002395; /* Dark blue background */
            color: white;
            font-family: Arial, sans-serif;
            margin: 0;
            display: flex;
            align-items: center;
            justify-content: space-between; /* Distribute content to the left and right */
            height: 100vh;
        }
        .left-container {
            display: flex;
            flex-direction: column;
            align-items: flex-start;
            padding: 20px;
        }
        .logo {
            width: 250px; /* Increased size of the logo */
            height: auto;
            position: absolute;
            top: 50px; /* Adjust as needed */
            left: 50px; 
        }
        .welcome-message {
            margin-top: 50px; /* Adjust spacing as needed */
            font-size: 50px;
        }
        .right-container {
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .extra-photo {
            width: 300px; /* Adjust size of the additional photo */
            height: auto;
            margin-right: auto;
        }
        .arrow-button {
            position: absolute;
            bottom: 20px;
            right: 20px;
            background-color: #007bff; /* Blue background for the circle */
            color: white;
            border: none;
            padding: 10px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            cursor: pointer;
        }

        .arrow-button svg {
            width: 16px; /* Size of the SVG chevron */
            height: auto;
            stroke: white;
            stroke-width: 2;
        }

        .arrow-button:hover {
            background-color: #0056b3; /* Darker blue on hover */
        }

    </style>
</head>
<body>
    <div class="left-container">
        <img src="{{ url_for('static', filename='dava_ninja_logo.png') }}" alt="Dava Ninja Logo" class="logo">
        <div class="welcome-message">Welcome to the maps portal of Dava Ninja</div>
    </div>
    <div class="right-container">
        <img src="{{ url_for('static', filename='Artboard.png') }}" alt="Artboard" class="extra-photo">
    </div>
    <button class="arrow-button" onclick="location.href='/map'">&#9654;</button> <!-- Right arrow symbol -->
</body>
</html>




this is landing html 

----------------------------------------------

this is the database 

pincode	count	Live	DEAD	latitude	longitude	type
400029	3	YES				
400049	2	YES				
400050	3	"YES
"				
400051	1	YES				
400052	3	YES				
400053	2	YES				
400054	1	YES				
400055	2	YES				
400056	2	YES				
400057	2	YES				
400058	2	YES				
400059	2	YES				
400060	5	YES				
400069	2	YES				
400072	1	YES				
400076	2	YES				
400093	2	YES				
400097	2	YES				
400098	2	YES				
400102	2	YES				
400104	2	YES				
400614	0		YES			
400710	0		YES			


----------------------------------------------------------------


now i want you to make changes in it and provide me the updated code i want to first see the landing html page and then index page and on index page the map should be able to fetch the location based from the database