"""
Database layer untuk PLTMG Sumbawa II Portal.

Menggunakan SQLAlchemy Async supaya siap dipakai dengan PostgreSQL (asyncpg).
Default konfigurasi memakai SQLite lokal agar aplikasi tetap berjalan sebelum
kredensial PostgreSQL production diaktifkan.

------------------------------------------------------------------
 CARA MENGGANTI KE POSTGRESQL
------------------------------------------------------------------
Edit file `/app/backend/.env` dan atur DATABASE_URL seperti contoh:

    DATABASE_URL="postgresql+asyncpg://dimasrme_pltmg:C0g1nd0%23@HOST:5432/dimasrme_pltmgsumbawa2"

Catatan:
- `C0g1nd0%23` = C0g1nd0# (karakter '#' harus di-URL-encode menjadi %23).
- Ganti HOST dengan host PostgreSQL Anda (mis. cpanel-server, localhost, dsb).
- Tabel akan dibuat otomatis saat backend start (init_db()).
"""
from __future__ import annotations

import os
from datetime import datetime, timezone
from typing import AsyncIterator

from sqlalchemy import Integer, String, Float, Date, DateTime, Text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

DATABASE_URL = os.environ.get("DATABASE_URL", "sqlite+aiosqlite:////app/backend/pltmg.db")

# check_same_thread hanya relevan untuk SQLite
engine_kwargs = {"echo": False, "future": True}
if DATABASE_URL.startswith("sqlite"):
    engine_kwargs["connect_args"] = {"check_same_thread": False}

engine = create_async_engine(DATABASE_URL, **engine_kwargs)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


class Base(DeclarativeBase):
    pass


class GalleryItem(Base):
    __tablename__ = "gallery_items"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(120), nullable=False)
    caption: Mapped[str] = mapped_column(Text, nullable=False)
    image_url: Mapped[str] = mapped_column(Text, nullable=False)
    category: Mapped[str] = mapped_column(String(60), nullable=False, default="Operasional")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "title": self.title,
            "caption": self.caption,
            "image_url": self.image_url,
            "category": self.category,
            "created_at": self.created_at.date().isoformat() if self.created_at else None,
        }


class PerformanceLog(Base):
    __tablename__ = "plant_performance_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    date: Mapped[str] = mapped_column(String(10), nullable=False)
    sfc: Mapped[float] = mapped_column(Float, nullable=False)
    sloc: Mapped[float] = mapped_column(Float, nullable=False)
    net_kwh: Mapped[float] = mapped_column(Float, nullable=False)
    fuel_consumption_liters: Mapped[float] = mapped_column(Float, nullable=False)
    avg_daily_load_kw: Mapped[float] = mapped_column(Float, nullable=False)
    running_hours_engine1: Mapped[float] = mapped_column(Float, nullable=False)
    running_hours_engine2: Mapped[float] = mapped_column(Float, nullable=False)
    running_hours_engine3: Mapped[float] = mapped_column(Float, nullable=False)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "date": self.date,
            "sfc": self.sfc,
            "sloc": self.sloc,
            "net_kwh": self.net_kwh,
            "fuel_consumption_liters": self.fuel_consumption_liters,
            "avg_daily_load_kw": self.avg_daily_load_kw,
            "running_hours_engine1": self.running_hours_engine1,
            "running_hours_engine2": self.running_hours_engine2,
            "running_hours_engine3": self.running_hours_engine3,
        }


async def get_session() -> AsyncIterator[AsyncSession]:
    async with SessionLocal() as session:
        yield session


SEED_GALLERY = [
    {
        "title": "Engine hall · Unit 01",
        "caption": "Wärtsilä 20V31DF siap menjaga pasokan Sistem Tambora.",
        "image_url": "https://images.unsplash.com/photo-1694160027547-b0696cf1ad45?auto=format&fit=crop&w=1200&q=85",
        "category": "Mesin Wartsila",
    },
    {
        "title": "Pusat kendali pembangkit",
        "caption": "Pemantauan operasi terpusat untuk respons yang cepat dan presisi.",
        "image_url": "https://images.pexels.com/photos/12270481/pexels-photo-12270481.jpeg?auto=compress&cs=tinysrgb&w=1200",
        "category": "Control Room",
    },
    {
        "title": "Switchyard Sumbawa II",
        "caption": "Energi disalurkan stabil untuk masyarakat Pulau Sumbawa.",
        "image_url": "https://images.pexels.com/photos/35596450/pexels-photo-35596450.jpeg?auto=compress&cs=tinysrgb&w=1200",
        "category": "Operasional",
    },
]

SEED_PERFORMANCE = [
    {"date": "2026-06-01", "sfc": 198.5, "sloc": 0.62, "net_kwh": 720000, "fuel_consumption_liters": 142800,
     "avg_daily_load_kw": 30000, "running_hours_engine1": 3420, "running_hours_engine2": 3390, "running_hours_engine3": 3415},
    {"date": "2026-06-02", "sfc": 197.8, "sloc": 0.60, "net_kwh": 725000, "fuel_consumption_liters": 143400,
     "avg_daily_load_kw": 30208, "running_hours_engine1": 3444, "running_hours_engine2": 3414, "running_hours_engine3": 3439},
]


async def init_db() -> None:
    """Create tables (idempotent) and seed initial demo data if kosong."""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    from sqlalchemy import select, func
    async with SessionLocal() as session:
        gallery_count = (await session.execute(select(func.count()).select_from(GalleryItem))).scalar() or 0
        if gallery_count == 0:
            for item in SEED_GALLERY:
                session.add(GalleryItem(**item))
        perf_count = (await session.execute(select(func.count()).select_from(PerformanceLog))).scalar() or 0
        if perf_count == 0:
            for item in SEED_PERFORMANCE:
                session.add(PerformanceLog(**item))
        await session.commit()