import streamlit as st
import pandas as pd
import json
import sqlite3
import os
from datetime import datetime, date
import folium
from streamlit_folium import st_folium
import io
import base64
from PIL import Image
import uuid

# Configuración de la página
st.set_page_config(
    page_title="Ojo nativo",
    page_icon="🦋",
    layout="wide",
    initial_sidebar_state="expanded"
)

# Inicializar base de datos SQLite
def init_db():
    conn = sqlite3.connect('biodiversity.db')
    cursor = conn.cursor()

    # Crear tabla de observaciones
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS observations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            species TEXT NOT NULL,
            common_name TEXT,
            family TEXT,
            order_tax TEXT,
            kingdom TEXT,
            lat REAL,
            lng REAL,
            region TEXT,
            date TEXT,
            observer TEXT,
            status TEXT,
            habitat TEXT,
            description TEXT,
            image_data TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # Crear tabla de usuarios/forum
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS forum_posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            author TEXT NOT NULL,
            species TEXT,
            lat REAL,
            lng REAL,
            image_data TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            likes INTEGER DEFAULT 0
        )
    """)

    conn.commit()
    conn.close()

# Cargar datos de taxonomía
@st.cache_data
def load_taxonomy():
    try:
        with open('taxonomy_data.json', 'r', encoding='utf-8') as f:
            return json.load(f)
    except:
        return {"Reino": {}}

# Cargar regiones de Argentina
@st.cache_data  
def load_regions():
    try:
        with open('argentina_regions.json', 'r', encoding='utf-8') as f:
            return json.load(f)
    except:
        return {}

# Obtener todas las especies de la taxonomía
def get_all_species(taxonomy_data):
    species = []
    for reino in taxonomy_data.get("Reino", {}).values():
        for phylum in reino.get("Phylum", {}).values():
            for clase in phylum.get("Clase", {}).values():
                for orden in clase.get("Orden", {}).values():
                    if "especies" in orden:
                        species.extend(orden["especies"])
    return species

# Función para convertir imagen a base64
def image_to_base64(image):
    buffer = io.BytesIO()
    image.save(buffer, format="PNG")
    return base64.b64encode(buffer.getvalue()).decode()

# Función para mostrar mapa
def create_map(observations_df, regions):
    # Centro de Argentina
    center_lat, center_lng = -38.416097, -63.616672
    m = folium.Map(location=[center_lat, center_lng], zoom_start=5)

    # Agregar observaciones al mapa
    for _, obs in observations_df.iterrows():
        if pd.notna(obs['lat']) and pd.notna(obs['lng']):
            popup_text = f"""
            <b>{obs['common_name']}</b><br>
            <i>{obs['species']}</i><br>
            Familia: {obs['family']}<br>
            Región: {obs['region']}<br>
            Fecha: {obs['date']}<br>
            Observador: {obs['observer']}
            """

            color = 'green' if obs['status'] == 'nativa_endemica' else 'blue'

            folium.Marker(
                location=[obs['lat'], obs['lng']],
                popup=folium.Popup(popup_text, max_width=300),
                tooltip=obs['common_name'],
                icon=folium.Icon(color=color, icon='leaf')
            ).add_to(m)

    # Agregar marcadores de regiones
    for region, coords in regions.items():
        folium.CircleMarker(
            location=[coords['lat'], coords['lng']],
            radius=8,
            popup=f"Región: {region}",
            color='red',
            fill=True,
            fillColor='red',
            fillOpacity=0.6
        ).add_to(m)

    return m

# Función principal
def main():
    init_db()

    # CSS personalizado
    st.markdown("""
    <style>
    .main-header {
        background: linear-gradient(90deg, #2E8B57, #228B22);
        padding: 1rem;
        border-radius: 10px;
        color: white;
        text-align: center;
        margin-bottom: 2rem;
    }
    .species-card {
        background: #2E8B57;
        padding: 1rem;
        border-radius: 5px;
        border-left: 5px solid #2E8B57;
        margin: 0.5rem 0;
    }
    .forum-post {
        background: #f9f9f9;
        padding: 1rem;
        border-radius: 8px;
        margin: 1rem 0;
        border: 1px solid #ddd;
    }
    </style>
    """, unsafe_allow_html=True)

    # Header principal
    st.markdown("""
    <div class="main-header">
        <h1>🌿 Ojo nativo - Sistema de Información de Biodiversidad 🦋</h1>
        <p>Conservación de Flora, Fauna y Fungus Nativas y Endémicas de Argentina</p>
    </div>
    """, unsafe_allow_html=True)

    # Sidebar para navegación
    st.sidebar.title("🧭 Navegación")
    page = st.sidebar.radio("Seleccionar sección:", [
        "🏠 Inicio",
        "🗺️ Mapa Interactivo", 
        "🔍 Buscador de Especies",
        "📷 Subir Observación",
        "💬 Foro Comunitario",
        "📊 Estadísticas",
        "📚 Base de Datos"
    ])

    # Cargar datos
    taxonomy_data = load_taxonomy()
    regions = load_regions()

    # Conectar a base de datos
    conn = sqlite3.connect('biodiversity.db')

    if page == "🏠 Inicio":
        col1, col2, col3 = st.columns(3)

        with col1:
            st.metric("Especies Registradas", "150+")
            st.metric("Observaciones", "1,247")

        with col2:
            st.metric("Regiones Cubiertas", "10")
            st.metric("Colaboradores", "89")

        with col3:
            st.metric("Especies Endémicas", "45")
            st.metric("En Peligro", "12")

        st.markdown("### 🎯 Objetivos del Proyecto")
        st.write("""
        - **Conservación**: Documentar y proteger la biodiversidad argentina
        - **Investigación**: Facilitar estudios científicos sobre especies nativas
        - **Educación**: Promover la conciencia ambiental
        - **Participación**: Involucrar a la comunidad en ciencia ciudadana
        """)

        st.markdown("### 📈 Últimas Actividades")
        recent_observations = pd.read_sql_query(
            "SELECT * FROM observations ORDER BY created_at DESC LIMIT 5", 
            conn
        )

        if not recent_observations.empty:
            for _, obs in recent_observations.iterrows():
                st.markdown(f"""
                <div class="species-card">
                    <b>{obs['common_name']}</b> (<i>{obs['species']}</i>)<br>
                    📍 {obs['region']} | 📅 {obs['date']} | 👤 {obs['observer']}
                </div>
                """, unsafe_allow_html=True)

    elif page == "🗺️ Mapa Interactivo":
        st.header("🗺️ Mapa de Observaciones de Biodiversidad")

        # Filtros
        col1, col2, col3 = st.columns(3)

        with col1:
            kingdom_filter = st.selectbox("Reino:", ["Todos"] + list(taxonomy_data.get("Reino", {}).keys()))

        with col2:
            region_filter = st.selectbox("Región:", ["Todas"] + list(regions.keys()))

        with col3:
            status_filter = st.selectbox("Estado:", ["Todos", "nativa_endemica", "nativa", "introducida"])

        # Cargar observaciones
        query = "SELECT * FROM observations WHERE 1=1"
        params = []

        if kingdom_filter != "Todos":
            query += " AND kingdom = ?"
            params.append(kingdom_filter)
        if region_filter != "Todas":
            query += " AND region = ?"
            params.append(region_filter)
        if status_filter != "Todos":
            query += " AND status = ?"
            params.append(status_filter)

        observations_df = pd.read_sql_query(query, conn, params=params)

        if not observations_df.empty:
            # Crear y mostrar mapa
            map_obj = create_map(observations_df, regions)
            st_folium(map_obj, width=700, height=500)

            st.write(f"**{len(observations_df)} observaciones encontradas**")
        else:
            st.info("No se encontraron observaciones con los filtros seleccionados.")

    elif page == "🔍 Buscador de Especies":
        st.header("🔍 Búsqueda Taxonómica")

        col1, col2 = st.columns(2)

        with col1:
            search_term = st.text_input("Buscar especie:", placeholder="Ej: Puma concolor")
            search_type = st.radio("Buscar por:", ["Nombre científico", "Nombre común", "Familia"])

        with col2:
            kingdom = st.selectbox("Reino:", ["Todos"] + list(taxonomy_data.get("Reino", {}).keys()))
            order_tax = st.text_input("Orden:", placeholder="Ej: Carnivora")

        if st.button("🔍 Buscar"):
            query = "SELECT * FROM observations WHERE 1=1"
            params = []

            if search_term:
                if search_type == "Nombre científico":
                    query += " AND species LIKE ?"
                    params.append(f"%{search_term}%")
                elif search_type == "Nombre común":
                    query += " AND common_name LIKE ?"
                    params.append(f"%{search_term}%")
                elif search_type == "Familia":
                    query += " AND family LIKE ?"
                    params.append(f"%{search_term}%")

            if kingdom != "Todos":
                query += " AND kingdom = ?"
                params.append(kingdom)

            if order_tax:
                query += " AND order_tax LIKE ?"
                params.append(f"%{order_tax}%")

            results = pd.read_sql_query(query, conn, params=params)

            if not results.empty:
                st.success(f"Se encontraron {len(results)} resultados:")

                for _, result in results.iterrows():
                    with st.expander(f"{result['common_name']} ({result['species']})"):
                        col1, col2 = st.columns(2)

                        with col1:
                            st.write(f"**Familia:** {result['family']}")
                            st.write(f"**Orden:** {result['order_tax']}")
                            st.write(f"**Reino:** {result['kingdom']}")
                            st.write(f"**Estado:** {result['status']}")

                        with col2:
                            st.write(f"**Región:** {result['region']}")
                            st.write(f"**Hábitat:** {result['habitat']}")
                            st.write(f"**Fecha:** {result['date']}")
                            st.write(f"**Observador:** {result['observer']}")

                        st.write(f"**Descripción:** {result['description']}")
            else:
                st.warning("No se encontraron resultados.")

    elif page == "📷 Subir Observación":
        st.header("📷 Nueva Observación de Biodiversidad")

        with st.form("observation_form"):
            col1, col2 = st.columns(2)

            with col1:
                species = st.text_input("Nombre científico*", placeholder="Ej: Puma concolor")
                common_name = st.text_input("Nombre común*", placeholder="Ej: Puma")
                family = st.text_input("Familia*", placeholder="Ej: Felidae")
                order_tax = st.text_input("Orden*", placeholder="Ej: Carnivora")
                kingdom = st.selectbox("Reino*", ["", "Animalia", "Plantae", "Fungi"])

            with col2:
                region = st.selectbox("Región*", [""] + list(regions.keys()))
                lat = st.number_input("Latitud", value=0.0, format="%.6f")
                lng = st.number_input("Longitud", value=0.0, format="%.6f")
                observation_date = st.date_input("Fecha de observación", value=date.today())
                observer = st.text_input("Nombre del observador*", placeholder="Tu nombre")

            status = st.selectbox("Estado de conservación", [
                "", "nativa_endemica", "nativa", "introducida", "en_peligro"
            ])
            habitat = st.text_input("Hábitat", placeholder="Ej: Bosque andino patagónico")
            description = st.text_area("Descripción", placeholder="Describe tu observación...")

            uploaded_file = st.file_uploader("Subir imagen", type=['png', 'jpg', 'jpeg'])

            submitted = st.form_submit_button("📤 Subir Observación")

            if submitted:
                if species and common_name and family and order_tax and kingdom and region and observer:
                    # Procesar imagen si fue subida
                    image_data = None
                    if uploaded_file is not None:
                        image = Image.open(uploaded_file)
                        # Redimensionar imagen
                        image.thumbnail((500, 500))
                        image_data = image_to_base64(image)

                    # Insertar en base de datos
                    cursor = conn.cursor()
                    cursor.execute("""
                        INSERT INTO observations 
                        (species, common_name, family, order_tax, kingdom, lat, lng, region, date, observer, status, habitat, description, image_data)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """, (species, common_name, family, order_tax, kingdom, lat, lng, region, 
                         str(observation_date), observer, status, habitat, description, image_data))

                    conn.commit()
                    st.success("✅ Observación registrada exitosamente!")
                    st.balloons()
                else:
                    st.error("Por favor completa todos los campos obligatorios (*)")

    elif page == "💬 Foro Comunitario":
        st.header("💬 Foro de la Comunidad")

        # Formulario para nuevo post
        with st.expander("✏️ Crear nueva publicación"):
            with st.form("forum_form"):
                title = st.text_input("Título*")
                content = st.text_area("Contenido*", height=100)
                author = st.text_input("Autor*")
                species = st.text_input("Especie relacionada (opcional)")

                col1, col2 = st.columns(2)
                with col1:
                    post_lat = st.number_input("Latitud (opcional)", value=0.0)
                with col2:
                    post_lng = st.number_input("Longitud (opcional)", value=0.0)

                forum_image = st.file_uploader("Imagen (opcional)", type=['png', 'jpg', 'jpeg'])

                if st.form_submit_button("📝 Publicar"):
                    if title and content and author:
                        image_data = None
                        if forum_image is not None:
                            image = Image.open(forum_image)
                            image.thumbnail((400, 400))
                            image_data = image_to_base64(image)

                        cursor = conn.cursor()
                        cursor.execute("""
                            INSERT INTO forum_posts (title, content, author, species, lat, lng, image_data)
                            VALUES (?, ?, ?, ?, ?, ?, ?)
                        """, (title, content, author, species, post_lat, post_lng, image_data))

                        conn.commit()
                        st.success("Publicación creada!")
                        st.rerun()
                    else:
                        st.error("Completa los campos obligatorios")

        # Mostrar posts del foro
        forum_posts = pd.read_sql_query(
            "SELECT * FROM forum_posts ORDER BY created_at DESC", conn
        )

        if not forum_posts.empty:
            for _, post in forum_posts.iterrows():
                st.markdown(f"""
                <div class="forum-post">
                    <h4>{post['title']}</h4>
                    <small>Por: {post['author']} | {post['created_at']}</small>
                    {f"<br><b>Especie:</b> {post['species']}" if post['species'] else ""}
                </div>
                """, unsafe_allow_html=True)

                st.write(post['content'])

                if post['image_data']:
                    try:
                        image_bytes = base64.b64decode(post['image_data'])
                        image = Image.open(io.BytesIO(image_bytes))
                        st.image(image, width=300)
                    except:
                        pass

                col1, col2, col3 = st.columns([1, 1, 4])
                with col1:
                    if st.button(f"👍 {post['likes']}", key=f"like_{post['id']}"):
                        cursor = conn.cursor()
                        cursor.execute("UPDATE forum_posts SET likes = likes + 1 WHERE id = ?", (post['id'],))
                        conn.commit()
                        st.rerun()

                st.divider()
        else:
            st.info("No hay publicaciones aún. ¡Sé el primero en compartir!")

    elif page == "📊 Estadísticas":
        st.header("📊 Estadísticas de Biodiversidad")

        # Consultas para estadísticas
        total_obs = pd.read_sql_query("SELECT COUNT(*) as count FROM observations", conn).iloc[0]['count']
        total_species = pd.read_sql_query("SELECT COUNT(DISTINCT species) as count FROM observations", conn).iloc[0]['count']
        total_families = pd.read_sql_query("SELECT COUNT(DISTINCT family) as count FROM observations", conn).iloc[0]['count']

        col1, col2, col3, col4 = st.columns(4)
        col1.metric("Total Observaciones", total_obs)
        col2.metric("Especies Únicas", total_species)
        col3.metric("Familias", total_families)
        col4.metric("Regiones Activas", len(regions))

        # Gráficos por reino
        if total_obs > 0:
            kingdom_stats = pd.read_sql_query(
                "SELECT kingdom, COUNT(*) as count FROM observations GROUP BY kingdom", conn
            )

            if not kingdom_stats.empty:
                st.subheader("Observaciones por Reino")
                st.bar_chart(kingdom_stats.set_index('kingdom'))

            # Top especies
            top_species = pd.read_sql_query(
                "SELECT species, common_name, COUNT(*) as observations FROM observations GROUP BY species ORDER BY observations DESC LIMIT 10", 
                conn
            )

            if not top_species.empty:
                st.subheader("Top 10 Especies Más Observadas")
                st.dataframe(top_species)

    elif page == "📚 Base de Datos":
        st.header("📚 Base de Datos Completa")

        # Mostrar todas las observaciones
        all_observations = pd.read_sql_query("SELECT * FROM observations ORDER BY created_at DESC", conn)

        if not all_observations.empty:
            st.write(f"Total de registros: {len(all_observations)}")

            # Filtros avanzados
            col1, col2, col3 = st.columns(3)

            with col1:
                filter_kingdom = st.multiselect("Filtrar por Reino:", all_observations['kingdom'].unique())
            with col2:
                filter_region = st.multiselect("Filtrar por Región:", all_observations['region'].unique())
            with col3:
                filter_status = st.multiselect("Filtrar por Estado:", all_observations['status'].unique())

            # Aplicar filtros
            filtered_df = all_observations.copy()

            if filter_kingdom:
                filtered_df = filtered_df[filtered_df['kingdom'].isin(filter_kingdom)]
            if filter_region:
                filtered_df = filtered_df[filtered_df['region'].isin(filter_region)]
            if filter_status:
                filtered_df = filtered_df[filtered_df['status'].isin(filter_status)]

            # Mostrar tabla
            st.dataframe(filtered_df, use_container_width=True)

            # Opción de descarga
            csv = filtered_df.to_csv(index=False)
            st.download_button(
                label="📥 Descargar datos (CSV)",
                data=csv,
                file_name=f"biodiversidad_argentina_{datetime.now().strftime('%Y%m%d')}.csv",
                mime="text/csv"
            )
        else:
            st.info("No hay datos en la base de datos aún.")

    # Cerrar conexión
    conn.close()

    # Footer
    st.markdown("---")
    st.markdown("""
    <div style='text-align: center; color: #666;'>
        🌱 Ojo nativo - Conservando la biodiversidad para las futuras generaciones 🌱<br>
        Desarrollado para la comunidad científica y educativa de Argentina
    </div>
    """, unsafe_allow_html=True)

if __name__ == "__main__":
    main()