Untitled

import streamlit as st
import psycopg2
import pandas as pd
from pathlib import Path

class CompetitorCategoryMapping:
    def __init__(self):
        self.conn = None
        self.df = None
        self.super_categories = []
        self.subsegments = []
        self.classes = []
        self.manufacturers = []

    def connect_to_db(self):
        try:
            self.conn = psycopg2.connect(
                host="e-harmonizer-dev.postgres.database.azure.com",
                port="5432",
                dbname="beveragecategorydemo",
                user="Harmonizer",
                password="dQp)18(*"
            )
            return True
        except Exception as e:
            st.error(f"Error connecting to the database: {e}")
            return False

    def fetch_unique_values(self, column_name):
        if self.connect_to_db():
            cursor = self.conn.cursor()
            cursor.execute(f'SELECT DISTINCT "{column_name}" FROM public.beverage_competitorcategorymappingtable')
            data = cursor.fetchall()
            cursor.close()
            self.conn.close()
            return [row[0] for row in data]
        return []

    def fetch_data(self, super_category, subsegment, class_selection, manufacturer):
        if self.connect_to_db():
            cursor = self.conn.cursor()
            if super_category and subsegment and class_selection and manufacturer:
                query = """
                SELECT "id", "Supercategory", "Sub Segment", "Class", "Corporate Manufacturer", "Competitor Item Id", "Competitor Item Description", "Modified Userid", "Modified Timestamp", "country"
                FROM public.beverage_competitorcategorymappingtable
                WHERE "Supercategory" = ANY(%s)
                AND "Sub Segment" = ANY(%s)
                AND "Class" = ANY(%s)
                AND "Corporate Manufacturer" = ANY(%s)
                """
                cursor.execute(query, (super_category, subsegment, class_selection, manufacturer))
                data = cursor.fetchall()
                columns = [desc[0] for desc in cursor.description]
                cursor.close()
                self.conn.close()
                return pd.DataFrame(data, columns=columns)
            else:
                st.info("Please select at least one value for each dropdown.")
            return None
        return None

    def update_data(self, record):
        if self.connect_to_db():
            cursor = self.conn.cursor()
            record_id = record.get('id')
            if record_id:
                super_category = record.get('Supercategory')
                subsegment = record.get('Sub Segment')
                class_selection = record.get('Class')
                query = """
                    UPDATE public.beverage_competitorcategorymappingtable
                    SET "Supercategory" = %s, "Sub Segment" = %s, "Class" = %s
                    WHERE "id" = %s
                """
                cursor.execute(query, (super_category, subsegment, class_selection, record_id))
                self.conn.commit()
                cursor.close()
                self.conn.close()
                return True
            else:
                st.error("Record ID is missing for the update.")
                return False

    def render(self):
        st.set_page_config(page_title="Competitor Category Mapping", page_icon=":chart_with_upwards_trend:")
        
        image_path = "C://Users//1752852//Downloads//theme1.jpg"  
        image_url = self.get_image_url(image_path) 
        st.markdown(
            f"""
            <style>
                .stApp {{
                    background: url('{image_url}') no-repeat center center fixed;
                    background-size: cover;
                }}
                .title {{
                    font-size: 24px;
                    color: #FF1493;
                    margin-top: 10px;
                    margin-left: 10px;
                    text-align: left;
                }}
                .subtitle {{
                    font-size: 30px;
                    color: #00008B;
                    text-align: center;
                }}
            </style>
            """,
            unsafe_allow_html=True
        )

        st.markdown("<h1 style='text-align: left; position: absolute; top:10px; left:10px; color: #FF1493; font-size:24px;'>TCS Data Harmonization Platform</h1>", unsafe_allow_html=True)
        st.markdown("<br>",unsafe_allow_html=True)
        st.markdown("<h1 style='text-align: center; color: #00008B; font-size:30px;'>Competitor Category Mapping</h1>", unsafe_allow_html=True)
        
        col1, col2, col3, col4 = st.columns([2, 2, 2, 2])
        with col1:
            # Dropdown selections fetched from database
            self.super_categories = self.fetch_unique_values('Supercategory')
            super_category = st.multiselect("Super Category", self.super_categories)
        with col2:
            self.subsegments = self.fetch_unique_values("Sub Segment")
            subsegment = st.multiselect("Subsegment", self.subsegments)
        with col3:
            self.classes = self.fetch_unique_values("Class")
            class_selection = st.multiselect("Class", self.classes)
        with col4:
            self.manufacturers = self.fetch_unique_values("Corporate Manufacturer")
            manufacturer = st.multiselect("Manufacturer", self.manufacturers)

        # Fetch data for editing based on dropdown selections
        summary_data = self.fetch_data(super_category, subsegment, class_selection, manufacturer)

        # Display the summary table with checkboxes
        if summary_data is not None:
            st.write("Summary Table:")
            # Integrate data editor with checkbox column
            st.dataframe(
                summary_data.assign(record="").set_index("record")
            )

    def get_image_url(self, image_path):
        image_url = Path(image_path).as_uri()
        return image_url

def main():
    mapping = CompetitorCategoryMapping()
    mapping.render()

if __name__ == "__main__":
    main()