import os
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

_DB_HOST = os.getenv("DB_HOST", "localhost")
_DB_PORT = os.getenv("DB_PORT", "5432")
_DB_NAME = os.getenv("DB_NAME", "homestead")
_DB_USER = os.getenv("DB_USER", "homestead")
_DB_PASSWORD = os.getenv("DB_PASSWORD", "homestead")

DATABASE_URL = (
    f"postgresql+psycopg2://{_DB_USER}:{_DB_PASSWORD}@{_DB_HOST}:{_DB_PORT}/{_DB_NAME}"
)

engine = create_engine(DATABASE_URL, pool_pre_ping=True)


def get_session():
    with Session(engine) as session:
        yield session


def init_db():
    """Create schemas and tables, then seed initial users."""
    from app.auth import seed_users

    with engine.connect() as conn:
        # Create auth schema and users table
        conn.execute(text("CREATE SCHEMA IF NOT EXISTS auth"))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS auth.users (
                id SERIAL PRIMARY KEY,
                username TEXT NOT NULL UNIQUE,
                hashed_password TEXT NOT NULL
            )
        """))

        # Create lk schema and tables
        conn.execute(text("CREATE SCHEMA IF NOT EXISTS lk"))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS lk.lists (
                id           SERIAL PRIMARY KEY,
                name         TEXT NOT NULL,
                type         TEXT NOT NULL CHECK (type IN ('shopping', 'todo', 'project')),
                t_shirt_size TEXT CHECK (t_shirt_size IN ('XS', 'S', 'M', 'L', 'XL')),
                status       TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),
                notes        TEXT,
                created_at   TIMESTAMPTZ DEFAULT now(),
                closed_at    TIMESTAMPTZ
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS lk.list_items (
                id         SERIAL PRIMARY KEY,
                list_id    INTEGER NOT NULL REFERENCES lk.lists(id) ON DELETE CASCADE,
                text       TEXT NOT NULL,
                checked    BOOLEAN NOT NULL DEFAULT FALSE,
                position   INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMPTZ DEFAULT now()
            )
        """))

        # Create public schema beekeeper tables
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS apiaries (
                id            SERIAL PRIMARY KEY,
                name          TEXT NOT NULL,
                location_text TEXT,
                lat           REAL,
                lng           REAL,
                notes         TEXT,
                created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS colonies (
                id               SERIAL PRIMARY KEY,
                apiary_id        INTEGER REFERENCES apiaries(id),
                name             TEXT NOT NULL,
                status           TEXT NOT NULL DEFAULT 'wirtschaftsvolk',
                parent_colony_id INTEGER REFERENCES colonies(id),
                current_queen_id INTEGER,
                notes            TEXT,
                created_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                closed_at        TIMESTAMPTZ
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS colony_locations (
                id        SERIAL PRIMARY KEY,
                colony_id INTEGER NOT NULL REFERENCES colonies(id),
                apiary_id INTEGER NOT NULL REFERENCES apiaries(id),
                moved_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS queens (
                id               SERIAL PRIMARY KEY,
                colony_id        INTEGER REFERENCES colonies(id),
                year             INTEGER,
                breed            TEXT,
                origin_colony_id INTEGER REFERENCES colonies(id),
                marked_color     TEXT,
                notes            TEXT,
                introduced_at    TEXT,
                superseded_at    TEXT,
                created_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS inspections (
                id                SERIAL PRIMARY KEY,
                colony_id         INTEGER NOT NULL REFERENCES colonies(id),
                date              TEXT NOT NULL,
                inspector         TEXT,
                brood_frames      INTEGER,
                honey_frames      INTEGER,
                food_stores_kg    REAL,
                colony_strength   INTEGER,
                queen_seen        INTEGER NOT NULL DEFAULT 0,
                eggs_seen         INTEGER NOT NULL DEFAULT 0,
                queen_cells_count INTEGER NOT NULL DEFAULT 0,
                notes             TEXT,
                created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS inspection_traits (
                id            SERIAL PRIMARY KEY,
                inspection_id INTEGER NOT NULL REFERENCES inspections(id) ON DELETE CASCADE,
                trait         TEXT NOT NULL,
                score         INTEGER NOT NULL CHECK (score BETWEEN 1 AND 4)
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS varroa_counts (
                id         SERIAL PRIMARY KEY,
                colony_id  INTEGER NOT NULL REFERENCES colonies(id),
                date_from  TEXT,
                date       TEXT NOT NULL,
                time       TEXT,
                method     TEXT NOT NULL,
                count      REAL NOT NULL,
                notes      TEXT,
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS varroa_treatments (
                id         SERIAL PRIMARY KEY,
                colony_id  INTEGER NOT NULL REFERENCES colonies(id),
                date       TEXT NOT NULL,
                time       TEXT,
                product    TEXT NOT NULL,
                method     TEXT,
                dosage     TEXT,
                notes      TEXT,
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS varroa_controls (
                id           SERIAL PRIMARY KEY,
                treatment_id INTEGER NOT NULL REFERENCES varroa_treatments(id) ON DELETE CASCADE,
                colony_id    INTEGER NOT NULL REFERENCES colonies(id),
                date         TEXT NOT NULL,
                time         TEXT,
                result       TEXT,
                notes        TEXT,
                created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS colony_actions (
                id                SERIAL PRIMARY KEY,
                colony_id         INTEGER NOT NULL REFERENCES colonies(id),
                date              TEXT NOT NULL,
                time              TEXT,
                type              TEXT NOT NULL,
                notes             TEXT,
                related_colony_id INTEGER REFERENCES colonies(id),
                created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS honey_harvests (
                id         SERIAL PRIMARY KEY,
                colony_id  INTEGER REFERENCES colonies(id),
                apiary_id  INTEGER REFERENCES apiaries(id),
                date       TEXT NOT NULL,
                time       TEXT,
                weight_kg  REAL NOT NULL,
                type       TEXT,
                notes      TEXT,
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS checklists (
                id         SERIAL PRIMARY KEY,
                name       TEXT NOT NULL,
                scope      TEXT NOT NULL DEFAULT 'colony',
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS checklist_items (
                id           SERIAL PRIMARY KEY,
                checklist_id INTEGER NOT NULL REFERENCES checklists(id) ON DELETE CASCADE,
                text         TEXT NOT NULL,
                position     INTEGER NOT NULL DEFAULT 0
            )
        """))
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS checklist_completions (
                id           SERIAL PRIMARY KEY,
                checklist_id INTEGER NOT NULL REFERENCES checklists(id),
                item_id      INTEGER NOT NULL REFERENCES checklist_items(id),
                scope_id     INTEGER NOT NULL,
                date         TEXT NOT NULL,
                completed_by TEXT,
                notes        TEXT,
                created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """))

        # Add columns that may be missing from older installs (idempotent)
        for stmt in [
            "ALTER TABLE varroa_counts ADD COLUMN IF NOT EXISTS date_from TEXT",
            "ALTER TABLE varroa_counts ADD COLUMN IF NOT EXISTS time TEXT",
            "ALTER TABLE varroa_treatments ADD COLUMN IF NOT EXISTS time TEXT",
            "ALTER TABLE varroa_controls ADD COLUMN IF NOT EXISTS time TEXT",
            "ALTER TABLE honey_harvests ADD COLUMN IF NOT EXISTS time TEXT",
            "ALTER TABLE colony_actions ADD COLUMN IF NOT EXISTS related_colony_id INTEGER REFERENCES colonies(id)",
        ]:
            try:
                conn.execute(text(stmt))
            except Exception:
                pass

        conn.commit()

    seed_users()