"""
Settings CRUD — DB operations only, no business logic.
"""
from datetime import datetime
from typing import Optional
from sqlalchemy.orm import Session, selectinload
from sqlalchemy import select, func
from src.apps.merchants.models.merchant_settings import MerchantSettings
from src.apps.merchants.models.merchant_contacts import MerchantContacts
from src.apps.merchants.models.merchant_location import MerchantLocations
from src.apps.merchants.models.merchant_discount import MerchantDiscount
from src.apps.settings.models.user_preferences import UserPreferences
from src.apps.settings.defaults import USER_PREFERENCE_DEFAULTS


def get_settings_by_group(group: str, merchant_id: int, db: Session) -> list:
    stmt = select(MerchantSettings).where(
        MerchantSettings.group == group,
        MerchantSettings.merchant_id == merchant_id,
    )
    return db.execute(stmt).scalars().all()


def upsert_setting(
    group: str,
    key: str,
    value: str,
    merchant_id: int,
    db: Session,
    label: str = "",
    field_type: str = "text",
) -> MerchantSettings:
    """UPDATE if exists, INSERT if not. Returns the setting."""
    stmt = select(MerchantSettings).where(
        MerchantSettings.group == group,
        MerchantSettings.key == key,
        MerchantSettings.merchant_id == merchant_id,
    )
    setting = db.execute(stmt).scalar_one_or_none()
    if setting:
        setting.value = value
    else:
        setting = MerchantSettings(
            group=group,
            key=key,
            value=value,
            label=label if label else key.replace("_", " ").title(),
            field_name=f"{group}_{key}",
            field_type=field_type,
            merchant_id=merchant_id,
        )
        db.add(setting)
    db.flush()
    return setting


def delete_settings_by_groups(groups: list, merchant_id: int, db: Session) -> int:
    """Delete all settings rows for given groups. Returns count deleted."""
    count = 0
    for group in groups:
        rows = db.execute(
            select(MerchantSettings).where(
                MerchantSettings.group == group,
                MerchantSettings.merchant_id == merchant_id,
            )
        ).scalars().all()
        for row in rows:
            db.delete(row)
            count += 1
    db.flush()
    return count


def seed_settings_for_merchant(
    merchant_id: int,
    groups: Optional[list],
    overwrite: bool,
    db: Session,
) -> dict:
    """Seed merchant_settings from MERCHANT_SETTINGS_DEFAULTS. Returns {seeded, skipped, groups}."""
    from src.apps.settings.defaults import MERCHANT_SETTINGS_DEFAULTS, ALL_SETTINGS_GROUPS
    target_groups = groups if groups is not None else ALL_SETTINGS_GROUPS
    seeded = 0
    skipped = 0
    for group in target_groups:
        defaults = MERCHANT_SETTINGS_DEFAULTS.get(group, {})
        for key, value in defaults.items():
            existing = db.execute(
                select(MerchantSettings).where(
                    MerchantSettings.group == group,
                    MerchantSettings.key == key,
                    MerchantSettings.merchant_id == merchant_id,
                )
            ).scalar_one_or_none()
            if existing and not overwrite:
                skipped += 1
            elif existing and overwrite:
                existing.value = value
                seeded += 1
            else:
                db.add(MerchantSettings(
                    group=group,
                    key=key,
                    value=value,
                    label=key.replace("_", " ").title(),
                    field_name=f"{group}_{key}",
                    field_type="text",
                    merchant_id=merchant_id,
                ))
                seeded += 1
    db.flush()
    return {"seeded": seeded, "skipped": skipped, "groups": target_groups}


# --- UserPreferences CRUD ---

def get_or_create_preferences(user_id: int, db: Session) -> UserPreferences:
    stmt = select(UserPreferences).where(UserPreferences.user_id == user_id)
    prefs = db.execute(stmt).scalar_one_or_none()
    if not prefs:
        prefs = UserPreferences(
            user_id=user_id,
            **USER_PREFERENCE_DEFAULTS,
        )
        db.add(prefs)
        db.flush()
    return prefs


_PREFERENCES_ALLOWED_FIELDS = frozenset({
    "date_format", "time_format", "timezone", "language",
    "currency_display", "notifications_email", "notifications_sms",
})


def update_preferences(user_id: int, updates: dict, db: Session) -> UserPreferences:
    prefs = get_or_create_preferences(user_id, db)
    for field, value in updates.items():
        if field not in _PREFERENCES_ALLOWED_FIELDS:
            # Silently ignore unknown fields — do not write arbitrary attributes
            # onto the ORM object (prevents accidental id/user_id/created_at clobbering).
            continue
        setattr(prefs, field, value)
    db.flush()
    return prefs


# --- MerchantContacts CRUD ---

def list_contacts(
    merchant_id: int,
    db: Session,
    page: int = 1,
    per_page: int = 10,
    search: Optional[str] = None,
) -> dict:
    base_filters = [
        MerchantContacts.merchant_id == merchant_id,
        MerchantContacts.deleted_at == None,
    ]
    if search:
        base_filters.append(
            MerchantContacts.name.ilike(f"%{search}%")
            | MerchantContacts.email.ilike(f"%{search}%")
            | MerchantContacts.phone.ilike(f"%{search}%")
        )
    total = db.execute(
        select(func.count(MerchantContacts.id)).where(*base_filters)
    ).scalar_one()
    offset = (page - 1) * per_page
    items = db.execute(
        select(MerchantContacts)
        .where(*base_filters)
        .order_by(MerchantContacts.id.desc())
        .offset(offset)
        .limit(per_page)
    ).scalars().all()
    last_page = max(1, (total + per_page - 1) // per_page)
    return {
        "data": items,
        "meta": {"current_page": page, "per_page": per_page, "total": total, "last_page": last_page},
    }


def get_contact(contact_id: int, merchant_id: int, db: Session) -> Optional[MerchantContacts]:
    stmt = select(MerchantContacts).where(
        MerchantContacts.id == contact_id,
        MerchantContacts.merchant_id == merchant_id,
        MerchantContacts.deleted_at == None,
    )
    return db.execute(stmt).scalar_one_or_none()


def create_contact(merchant_id: int, data: dict, db: Session) -> MerchantContacts:
    contact = MerchantContacts(merchant_id=merchant_id, **data)
    db.add(contact)
    db.flush()
    return contact


def update_contact(
    contact_id: int, merchant_id: int, data: dict, db: Session
) -> Optional[MerchantContacts]:
    contact = get_contact(contact_id, merchant_id, db)
    if not contact:
        return None
    for field, value in data.items():
        setattr(contact, field, value)
    db.flush()
    return contact


def soft_delete_contact(contact_id: int, merchant_id: int, db: Session) -> bool:
    contact = get_contact(contact_id, merchant_id, db)
    if not contact:
        return False
    contact.deleted_at = datetime.utcnow()
    db.flush()
    return True


# --- MerchantDiscount CRUD ---

def list_discounts(
    merchant_id: int,
    search: Optional[str],
    is_active: Optional[bool],
    db: Session,
) -> list:
    stmt = select(MerchantDiscount).where(
        MerchantDiscount.merchant_id == merchant_id,
        MerchantDiscount.deleted_at == None,
    )
    if search:
        stmt = stmt.where(MerchantDiscount.title.ilike(f"%{search}%"))
    if is_active is not None:
        stmt = stmt.where(MerchantDiscount.is_active == is_active)
    return db.execute(stmt).scalars().all()


def get_discount(discount_id: int, merchant_id: int, db: Session) -> Optional[MerchantDiscount]:
    stmt = select(MerchantDiscount).where(
        MerchantDiscount.id == discount_id,
        MerchantDiscount.merchant_id == merchant_id,
        MerchantDiscount.deleted_at == None,
    )
    return db.execute(stmt).scalar_one_or_none()


def create_discount(
    merchant_id: int, user_id: int, data: dict, db: Session
) -> MerchantDiscount:
    discount = MerchantDiscount(
        merchant_id=merchant_id,
        created_by_id=user_id,
        discount_id=f"disc_{merchant_id}_{int(datetime.utcnow().timestamp())}",
        **data,
    )
    db.add(discount)
    db.flush()
    return discount


def update_discount(
    discount_id: int, merchant_id: int, data: dict, db: Session
) -> Optional[MerchantDiscount]:
    discount = get_discount(discount_id, merchant_id, db)
    if not discount:
        return None
    for field, value in data.items():
        setattr(discount, field, value)
    db.flush()
    return discount


def soft_delete_discount(discount_id: int, merchant_id: int, db: Session) -> bool:
    discount = get_discount(discount_id, merchant_id, db)
    if not discount:
        return False
    discount.deleted_at = datetime.utcnow()
    db.flush()
    return True


# --- MerchantLocations CRUD ---

def list_locations(
    merchant_id: int,
    db: Session,
    page: int = 1,
    per_page: int = 10,
    search: Optional[str] = None,
) -> dict:
    base_filters = [
        MerchantLocations.merchant_id == merchant_id,
        MerchantLocations.deleted_at == None,
    ]
    if search:
        base_filters.append(
            MerchantLocations.name.ilike(f"%{search}%")
            | MerchantLocations.email.ilike(f"%{search}%")
            | MerchantLocations.phone.ilike(f"%{search}%")
        )
    total = db.execute(
        select(func.count(MerchantLocations.id)).where(*base_filters)
    ).scalar_one()
    offset = (page - 1) * per_page
    items = db.execute(
        select(MerchantLocations)
        .options(selectinload(MerchantLocations.address))
        .where(*base_filters)
        .order_by(MerchantLocations.id.desc())
        .offset(offset)
        .limit(per_page)
    ).scalars().all()
    last_page = max(1, (total + per_page - 1) // per_page)
    return {
        "data": items,
        "meta": {"current_page": page, "per_page": per_page, "total": total, "last_page": last_page},
    }


def get_location(location_id: int, merchant_id: int, db: Session) -> Optional[MerchantLocations]:
    stmt = (
        select(MerchantLocations)
        .options(selectinload(MerchantLocations.address))
        .where(
            MerchantLocations.id == location_id,
            MerchantLocations.merchant_id == merchant_id,
            MerchantLocations.deleted_at == None,
        )
    )
    return db.execute(stmt).scalar_one_or_none()


def create_location(merchant_id: int, data: dict, db: Session) -> MerchantLocations:
    location = MerchantLocations(merchant_id=merchant_id, **data)
    db.add(location)
    db.flush()
    return location


def update_location(
    location_id: int, merchant_id: int, data: dict, db: Session
) -> Optional[MerchantLocations]:
    location = get_location(location_id, merchant_id, db)
    if not location:
        return None
    for field, value in data.items():
        setattr(location, field, value)
    db.flush()
    return location


def soft_delete_location(location_id: int, merchant_id: int, db: Session) -> bool:
    location = get_location(location_id, merchant_id, db)
    if not location:
        return False
    location.deleted_at = datetime.utcnow()
    db.flush()
    return True