"""Database abstraction layer supporting both SQLite and PostgreSQL.

RH Planète uses SQLite for local development and verification by default,
and supports PostgreSQL in production via DATABASE_URL or RH_DATABASE.
"""
from __future__ import annotations

import os
import re
import sqlite3
from datetime import date, datetime
from decimal import Decimal
from pathlib import Path
from urllib.parse import urlparse

ROOT = Path(__file__).resolve().parent
DEFAULT_SQLITE_PATH = ROOT / "instance" / "rh.sqlite3"

try:
    import psycopg2
    import psycopg2.extras
    HAS_PSYCOPG2 = True
except ImportError:
    HAS_PSYCOPG2 = False


def get_db_uri() -> str:
    return (os.environ.get("DATABASE_URL") or os.environ.get("RH_DATABASE")
            or ("postgresql://" if os.environ.get('PGDATABASE') else str(DEFAULT_SQLITE_PATH)))


def is_postgres_uri(uri: str) -> bool:
    return uri.startswith("postgresql://") or uri.startswith("postgres://")


class CursorWrapper:
    """Wraps DB cursors to provide uniform dict/index access and lastrowid."""
    def __init__(self, raw_cursor, is_pg: bool = False):
        self.cursor = raw_cursor
        self.is_pg = is_pg
        self._insert_id = None

    def _normalize(self, row):
        if row is None or not self.is_pg:
            return row
        # Preserve the SQLite-facing contract used by services and templates.
        for key in row.keys():
            if isinstance(row[key], (date, datetime)):
                row[key] = row[key].isoformat()
            elif isinstance(row[key], Decimal):
                row[key] = float(row[key])
        return row

    def fetchone(self):
        row = self.cursor.fetchone()
        return self._normalize(row)

    def fetchall(self):
        return [self._normalize(row) for row in self.cursor.fetchall()]

    @property
    def lastrowid(self):
        if not self.is_pg:
            return getattr(self.cursor, "lastrowid", None)
        if self._insert_id is not None:
            return self._insert_id
        try:
            # If RETURNING id was used
            row = self.cursor.fetchone()
            if row:
                self._insert_id = row[0]
                return self._insert_id
        except Exception:
            pass
        return None

    def __iter__(self):
        return iter(self.fetchall())


class DBConnection:
    """Unified wrapper around sqlite3 or psycopg2 connection."""
    def __init__(self, uri: str | None = None):
        self.uri = uri or get_db_uri()
        self.is_pg = is_postgres_uri(self.uri)
        if self.is_pg:
            if not HAS_PSYCOPG2:
                raise RuntimeError("psycopg2 is required for PostgreSQL. Run: pip install psycopg2-binary")
            options = dict(cursor_factory=psycopg2.extras.DictCursor, connect_timeout=10)
            if self.uri == 'postgresql://':
                self.conn = psycopg2.connect(dbname=os.environ['PGDATABASE'],
                    user=os.environ.get('PGUSER'), password=os.environ.get('PGPASSWORD'),
                    host=os.environ.get('PGHOST', 'localhost'), port=os.environ.get('PGPORT', '5432'), **options)
            else:
                self.conn = psycopg2.connect(self.uri, **options)
        else:
            db_path = Path(self.uri)
            db_path.parent.mkdir(parents=True, exist_ok=True)
            self.conn = sqlite3.connect(db_path)
            self.conn.row_factory = sqlite3.Row
            self.conn.execute("PRAGMA foreign_keys=ON")

    def execute(self, sql_query: str, params: tuple | list = ()):
        if self.is_pg:
            # Translate '?' placeholder to '%s' for PostgreSQL
            # Handle RETURNING id for INSERT queries if lastrowid might be needed
            pg_sql = sql_query.replace("?", "%s")
            match = re.match(r'\s*INSERT\s+INTO\s+(\w+)', pg_sql, re.I)
            if match and match.group(1).lower() in {'users','leave_requests','weekend_work','audit_log','monthly_salaries','payroll_adjustments','public_holidays'} and 'RETURNING' not in pg_sql.upper():
                pg_sql = pg_sql.rstrip().rstrip(';') + ' RETURNING id'
            cur = self.conn.cursor()
            cur.execute(pg_sql, params)
            return CursorWrapper(cur, is_pg=True)
        else:
            cur = self.conn.execute(sql_query, params)
            return CursorWrapper(cur, is_pg=False)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def lock_person(self, user_id):
        """Serialize requests and balance decisions for one employee."""
        if self.is_pg:
            self.execute('SELECT id FROM users WHERE id=? FOR UPDATE', (user_id,)).fetchone()
        else:
            self.execute('BEGIN IMMEDIATE')

    def close(self):
        self.conn.close()

    def executescript(self, script: str):
        if self.is_pg:
            with self.conn.cursor() as cur:
                cur.execute(script)
            self.conn.commit()
        else:
            self.conn.executescript(script)
            self.conn.commit()