"""Upload specified sheets from local_content_dir/log_book.xlsx into the database table public.log_book.

Behavior:
- Reads each sheet in the provided list (currently set to ["XH-01"]).
- Uses the first row of each sheet as column names; expected to match the existing DB table columns.
- Appends a fixed column "vehicle" with the value equal to the sheet name.
- Skips fully-empty rows.

Notes:
- The script uses src.pg_handler.db_handler.PgHandler and src.config.load_config.project_config for DB connection info.
- This script performs INSERTs; run with a backup of the workbook if needed.
"""
import os

from pathlib import Path
from typing import List, Optional
from datetime import datetime, date
from openpyxl import load_workbook

from src.PostgresSQL.db_handler import PgHandler
from src.config.load_config import project_config

PROJECT_ROOT = Path(__file__).resolve().parents[4]
KSP_DATA_DIR = os.path.join(PROJECT_ROOT, "data", "KSP_data")

def _parse_excel_date(value):
    if value is None or value == "":
        return None
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    if isinstance(value, (int, float)):
        return None
    if isinstance(value, str):
        text = value.strip()
        if not text:
            return None
        for fmt in ("%Y-%m-%d", "%Y/%m/%d", "%Y.%m.%d", "%Y-%m-%d %H:%M:%S"):
            try:
                return datetime.strptime(text, fmt).date()
            except Exception:
                continue
    return None


def _read_sheet(ws):
    """Read header row and data rows from worksheet. Returns (headers, rows).

    headers: list of header names (strings)
    rows: list of lists of cell values (preserving original types where possible)
    """
    headers: List[str] = []
    for col in range(1, ws.max_column + 1):
        raw = ws.cell(row=1, column=col).value
        if raw is None:
            headers.append("")
        else:
            headers.append(str(raw).strip())

    data_rows: List[List[Optional[object]]] = []
    for r in range(2, ws.max_row + 1):
        row_vals: List[Optional[object]] = []
        non_empty = False
        for c in range(1, ws.max_column + 1):
            v = ws.cell(row=r, column=c).value
            if isinstance(v, (datetime, date)):
                v = _parse_excel_date(v)
            elif isinstance(v, str):
                v = v.strip()
            if v not in (None, ""):
                non_empty = True
            row_vals.append(v)
        if non_empty:
            data_rows.append(row_vals)
    return headers, data_rows


def upload_sheets(sheet_names: List[str], workbook_path) -> None:
    wb_path = Path(workbook_path)
    if not wb_path.exists():
        raise FileNotFoundError(f"Workbook not found: {wb_path}")

    wb = load_workbook(wb_path, data_only=True)

    # DB connection info from project_config
    host = project_config.get('db_host')
    port = project_config.get('db_port')
    user = project_config.get('db_user')
    password = project_config.get('db_password')
    database = project_config.get('db_name')

    with PgHandler(host=host, port=port, user=user, password=password or None, database=database) as pg:
        conn = pg._get_conn()
        cur = conn.cursor()

        for sheet_name in sheet_names:
            if sheet_name not in wb.sheetnames:
                print(f"Sheet not found in workbook: {sheet_name}, skipping")
                continue
            ws = wb[sheet_name]
            headers, rows = _read_sheet(ws)
            if not headers or not any(h for h in headers):
                print(f"No headers found in sheet {sheet_name}, skipping")
                continue

            # Prepare columns: take non-empty header names only
            cols = [h for h in headers if h]
            if not cols:
                print(f"Sheet {sheet_name} has no named columns, skipping")
                continue

            # final columns include vehicle
            db_columns = cols + ["vehicle"]
            col_sql = ", ".join([f'"{c}"' for c in db_columns])
            placeholders = ", ".join(["%s"] * len(db_columns))
            insert_sql = f'INSERT INTO "public"."log_book" ({col_sql}) VALUES ({placeholders})'

            # Build values: map each row (which may contain more columns than headers length) to cols
            values_to_insert = []
            for row_vals in rows:
                # Truncate/extend row_vals to match number of defined headers
                mapped = []
                for i, col_name in enumerate(cols):
                    if i < len(row_vals):
                        mapped.append(row_vals[i])
                    else:
                        mapped.append(None)
                mapped.append(sheet_name)  # vehicle
                values_to_insert.append(tuple(mapped))

            if not values_to_insert:
                print(f"No data rows for sheet {sheet_name}, skipping insert")
                continue

            try:
                cur.executemany(insert_sql, values_to_insert)
                conn.commit()
                print(f"Inserted {len(values_to_insert)} rows from sheet {sheet_name} into log_book")
            except Exception as e:
                conn.rollback()
                print(f"Failed to insert rows from sheet {sheet_name}: {e}")

        cur.close()


if __name__ == "__main__":
    WORKBOOK_PATH = os.path.join(KSP_DATA_DIR, 'log_book.xlsx')
    SHEETS_TO_UPLOAD: List[str] = ["XH-01"]
    upload_sheets(SHEETS_TO_UPLOAD, WORKBOOK_PATH)

