#!/usr/bin/env python3
"""
Acceso a SQLite desde Python (CREATE + READ)
Equivalente al ejemplo Java: acceso-a-sqlite-via-java.jar
"""

import sqlite3
import os

DB_NAME = "dam.db"


def conectar():
    conn = sqlite3.connect(DB_NAME)
    print(f"Conexión establecida: {os.path.abspath(DB_NAME)}")
    return conn


def crear_tablas(conn):
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS profesores (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nombre TEXT NOT NULL,
            asignatura TEXT NOT NULL
        )
    """)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS alumnos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nombre TEXT NOT NULL,
            edad INTEGER NOT NULL,
            id_profesor INTEGER,
            FOREIGN KEY (id_profesor) REFERENCES profesores(id)
        )
    """)
    conn.commit()
    print("Tablas creadas exitosamente.")


def insertar_datos(conn):
    cursor = conn.cursor()

    profesores = [
        ("Pedro García",    "Matemáticas"),
        ("Laura Martínez",  "Física"),
        ("José Rodríguez",  "Química"),
    ]
    cursor.executemany("INSERT INTO profesores (nombre, asignatura) VALUES (?, ?)", profesores)

    alumnos = [
        ("Carlos Pérez",    20, 1),
        ("Ana López",       22, 2),
        ("Luis Fernández",  21, 3),
    ]
    cursor.executemany("INSERT INTO alumnos (nombre, edad, id_profesor) VALUES (?, ?, ?)", alumnos)

    conn.commit()
    print("Datos insertados.")


def consultar(conn):
    cursor = conn.cursor()
    cursor.execute("""
        SELECT a.nombre AS alumno, p.nombre AS profesor
        FROM alumnos a
        JOIN profesores p ON a.id_profesor = p.id
    """)
    print("\nAlumnos y sus profesores:")
    for alumno, profesor in cursor.fetchall():
        print(f"  Alumno: {alumno} | Profesor: {profesor}")


def main():
    print("Hello and welcome!\n")

    if os.path.exists(DB_NAME):
        os.remove(DB_NAME)

    conn = conectar()
    try:
        crear_tablas(conn)      # CREATE estructura
        insertar_datos(conn)    # CREATE datos
        consultar(conn)         # READ
    finally:
        conn.close()
        print("\nConexión cerrada.")


if __name__ == "__main__":
    main()