from datetime import date, datetime
from calendar import monthrange
from decimal import Decimal, ROUND_HALF_UP
from sqlalchemy.orm import Session
from app.models.accounting import AccountingPeriod, JournalLine, AccountBalance
from app.models.finance import Account, JournalEntry
from app.models.audit import AuditLog

MONEY_Q = Decimal("0.01")


def d(value) -> Decimal:
    return Decimal(str(value)).quantize(MONEY_Q, rounding=ROUND_HALF_UP)


ALLOWED_SOURCES = {
    "journal", "opening", "sale", "purchase", "expense", "income",
    "payment", "loan", "transfer", "stock_adjustment", "production",
}

COA_TEMPLATE = [
    ("1010", "Cash on Hand", "asset", "D", "cash"),
    ("1020", "Bank Accounts", "asset", "D", "bank"),
    ("1030", "Accounts Receivable - Customers", "asset", "D", "receivable"),
    ("1040", "Inventory - Plants", "asset", "D", "inventory"),
    ("1045", "Inventory - Seedlings & Accessories", "asset", "D", "inventory"),
    ("1050", "Work In Progress - Production Batches", "asset", "D", "inventory"),
    ("1060", "Prepaid Expenses", "asset", "D", "prepaid"),
    ("1200", "Fixed Assets", "asset", "D", "fixed_asset"),
    ("1210", "Accumulated Depreciation", "asset", "C", "contra_asset"),
    ("2010", "Accounts Payable - Suppliers", "liability", "C", "payable"),
    ("2020", "VAT/Withholding Payable", "liability", "C", "payable"),
    ("2030", "Loans Payable", "liability", "C", "loan"),
    ("2040", "Accrued Expenses", "liability", "C", "accrual"),
    ("2050", "Customer Advances", "liability", "C", "advance"),
    ("3010", "Opening Balance Equity", "equity", "C", "equity"),
    ("3020", "Owner's Capital", "equity", "C", "equity"),
    ("3030", "Owner's Drawings", "equity", "D", "equity"),
    ("3090", "Retained Earnings", "equity", "C", "equity"),
    ("4010", "Sales - Retail (plants)", "revenue", "C", "sales"),
    ("4020", "Sales - Wholesale", "revenue", "C", "sales"),
    ("4030", "Sales - Seed & Agro Inputs", "revenue", "C", "sales"),
    ("4040", "Sales Returns & Allowances", "revenue", "D", "sales_contra"),
    ("4050", "Discounts Given", "revenue", "D", "sales_contra"),
    ("4060", "Other Income / Services", "revenue", "C", "income"),
    ("5010", "COGS - Plants", "expense", "D", "cogs"),
    ("5020", "COGS - Accessories", "expense", "D", "cogs"),
    ("5030", "Purchase Discounts", "expense", "C", "cogs_contra"),
    ("6010", "Rent", "expense", "D", "expense"),
    ("6020", "Salary & Wages", "expense", "D", "expense"),
    ("6030", "Transport & Fuel", "expense", "D", "expense"),
    ("6040", "Electricity & Water", "expense", "D", "expense"),
    ("6050", "Internet & Communication", "expense", "D", "expense"),
    ("6060", "Marketing & Advertising", "expense", "D", "expense"),
    ("6070", "Packaging", "expense", "D", "expense"),
    ("6080", "Seeds & Propagation Material", "expense", "D", "expense"),
    ("6090", "Potting Media", "expense", "D", "expense"),
    ("6100", "Fertilizer & Nutrients", "expense", "D", "expense"),
    ("6110", "Plant Treatments & Agrochemicals", "expense", "D", "expense"),
    ("6120", "Nursery Overhead", "expense", "D", "expense"),
    ("6130", "Plant Loss / Mortality", "expense", "D", "expense"),
    ("6140", "Maintenance & Repairs", "expense", "D", "expense"),
    ("6150", "Bank Charges & Interest Expense", "expense", "D", "expense"),
    ("6160", "Depreciation", "expense", "D", "expense"),
    ("6990", "Other Expenses", "expense", "D", "expense"),
]

BRANCH_CASH_CATEGORIES = ("cash", "bank")


def seed_chart_of_accounts(db: Session):
    for code, name, account_type, normal_balance, category in COA_TEMPLATE:
        if not db.query(Account).filter(Account.code == code).first():
            db.add(Account(
                code=code, name=name, type=account_type,
                account_type=account_type, normal_balance=normal_balance,
                category=category, is_system=True, is_active=True,
            ))
    db.flush()
    from app.models.branch import Branch
    branches = db.query(Branch).filter(Branch.is_active == True).all()  # noqa: E712
    for parent_code in ("1010", "1020"):
        parent = db.query(Account).filter(Account.code == parent_code).first()
        if not parent:
            continue
        for branch in branches:
            child_code = f"{parent_code}-{branch.code}"
            if not db.query(Account).filter(Account.code == child_code).first():
                db.add(Account(
                    code=child_code,
                    name=f"{parent.name} - {branch.name}",
                    type=parent.type,
                    account_type=parent.account_type,
                    normal_balance=parent.normal_balance,
                    category=parent.category,
                    parent_id=parent.id,
                    branch_id=branch.id,
                    is_system=True,
                    is_active=True,
                ))
    db.flush()


class AccountingError(Exception):
    pass


def generate_entry_number(db: Session) -> str:
    year = datetime.now().year
    last = db.query(JournalEntry).filter(
        JournalEntry.entry_number.like(f"JV-{year}-%")
    ).order_by(JournalEntry.entry_number.desc()).first()
    if last:
        seq = int(last.entry_number.split("-")[-1]) + 1
    else:
        seq = 1
    return f"JV-{year}-{seq:06d}"


def period_bounds(entry_date: date) -> tuple[date, date, str]:
    code = entry_date.strftime("%Y-%m")
    start = entry_date.replace(day=1)
    last_day = monthrange(entry_date.year, entry_date.month)[1]
    end = entry_date.replace(day=last_day)
    return start, end, code


class PostingService:
    def __init__(self, db: Session):
        self.db = db

    def get_or_create_period(self, entry_date: date) -> AccountingPeriod:
        start, end, code = period_bounds(entry_date)
        period = self.db.query(AccountingPeriod).filter(
            AccountingPeriod.period_code == code
        ).first()
        if period:
            return period
        period = AccountingPeriod(
            period_code=code, start_date=start, end_date=end, status="open",
        )
        self.db.add(period)
        self.db.flush()
        return period

    def validate_period_open(self, entry: JournalEntry):
        period = self.db.query(AccountingPeriod).filter(
            AccountingPeriod.id == entry.period_id
        ).first()
        if not period:
            raise AccountingError("Accounting period not found")
        if period.status != "open":
            raise AccountingError(f"Period {period.period_code} is closed")
        if entry.entry_date < period.start_date or entry.entry_date > period.end_date:
            raise AccountingError(f"Entry date {entry.entry_date} falls outside period {period.period_code}")

    def _check_balance(self, lines):
        total_debit = sum((d(line.get("debit") or 0) for line in lines), Decimal("0"))
        total_credit = sum((d(line.get("credit") or 0) for line in lines), Decimal("0"))
        if total_debit != total_credit:
            raise AccountingError(
                f"Journal is unbalanced: debits {total_debit} != credits {total_credit}"
            )
        if total_debit == 0:
            raise AccountingError("Journal lines are empty")
        return total_debit

    def create_draft(self, *, entry_date: date, lines, description: str,
                     branch_id: str | None = None, source: str = "journal",
                     reference_type: str | None = None, reference_id: str | None = None,
                     created_by: str) -> JournalEntry:
        if source not in ALLOWED_SOURCES:
            raise AccountingError(f"Unknown source '{source}'")
        valid = [ln for ln in lines if d(ln.get("debit") or 0) or d(ln.get("credit") or 0)]
        self._check_balance(valid)
        period = self.get_or_create_period(entry_date)
        for ln in valid:
            account = self.db.query(Account).filter(Account.id == ln["account_id"]).first()
            if not account:
                raise AccountingError(f"Account {ln['account_id']} not found")
            if not account.is_active:
                raise AccountingError(f"Account {account.name} is inactive")
        entry = JournalEntry(
            entry_number=generate_entry_number(self.db),
            entry_date=entry_date,
            period_id=period.id,
            branch_id=branch_id,
            status="draft",
            source=source,
            reference_type=reference_type,
            reference_id=reference_id,
            description=description,
            created_by=created_by,
        )
        self.db.add(entry)
        self.db.flush()
        for ln in valid:
            self.db.add(JournalLine(
                entry_id=entry.id,
                account_id=ln["account_id"],
                debit=d(ln.get("debit") or 0),
                credit=d(ln.get("credit") or 0),
                branch_id=ln.get("branch_id") or branch_id,
                production_batch_id=ln.get("production_batch_id"),
                passport_id=ln.get("passport_id"),
                cost_center=ln.get("cost_center"),
            ))
        self._audit("create_journal", entry, created_by)
        self.db.flush()
        return entry

    def _audit(self, action: str, entry: JournalEntry, user_id: str, detail: str | None = None):
        self.db.add(AuditLog(
            user_id=user_id, action=action, module="accounting",
            record_id=entry.id,
            new_value=detail or (
                f"{entry.entry_number} {entry.description} "
                f"source={entry.source} status={entry.status}"
            ),
        ))

    def _apply_balances(self, entry: JournalEntry) -> None:
        period = self.db.query(AccountingPeriod).filter(AccountingPeriod.id == entry.period_id).first()
        lookup = {}
        for line in entry.lines:
            branch = line.branch_id or entry.branch_id or ""
            key = (line.account_id, entry.period_id, branch)
            balance = lookup.get(key)
            if balance is None:
                balance = self.db.query(AccountBalance).filter(
                    AccountBalance.account_id == line.account_id,
                    AccountBalance.period_id == entry.period_id,
                    AccountBalance.branch_id == branch,
                ).first()
                if balance is None:
                    prev_opening = Decimal("0")
                    if period:
                        prev = self.db.query(AccountBalance).filter(
                            AccountBalance.account_id == line.account_id,
                            AccountBalance.branch_id == branch,
                            AccountBalance.period_id.in_(
                                self.db.query(AccountingPeriod.id).filter(
                                    AccountingPeriod.period_code < period.period_code
                                )
                            ),
                        ).order_by(
                            self.db.query(AccountingPeriod.period_code).filter(
                                AccountingPeriod.id == AccountBalance.period_id
                            ).label("pc").desc()
                        ).first()
                        if prev:
                            prev_opening = Decimal(str(prev.closing_balance))
                    balance = AccountBalance(
                        account_id=line.account_id,
                        period_id=entry.period_id,
                        branch_id=branch,
                        opening_balance=prev_opening,
                    )
                    self.db.add(balance)
                    self.db.flush()
                lookup[key] = balance
            balance.total_debit = d(Decimal(str(balance.total_debit)) + d(line.debit))
            balance.total_credit = d(Decimal(str(balance.total_credit)) + d(line.credit))
            account = self.db.query(Account).filter(Account.id == line.account_id).first()
            normal = (account.normal_balance if account else None) or "D"
            net = balance.total_debit - balance.total_credit
            if normal == "C":
                net = -net
            balance.closing_balance = d(Decimal(str(balance.opening_balance)) + net)

    def post_entry(self, entry_id: str, user_id: str) -> JournalEntry:
        entry = self.db.query(JournalEntry).filter(JournalEntry.id == entry_id).first()
        if not entry:
            raise AccountingError("Journal entry not found")
        if entry.status != "draft":
            raise AccountingError(f"Only draft entries can be posted (status={entry.status})")
        self.validate_period_open(entry)
        lines = [{"debit": ln.debit, "credit": ln.credit} for ln in entry.lines]
        self._check_balance(lines)
        entry.status = "posted"
        entry.posted_by = user_id
        entry.posted_at = datetime.now()
        self._apply_balances(entry)
        self._audit("post_journal", entry, user_id)
        self.db.flush()
        return entry

    def post_event(self, *, source: str, reference_type: str | None,
                   reference_id: str | None, entry_date: date,
                   branch_id: str | None, lines, description: str,
                   created_by: str) -> JournalEntry:
        existing = self.db.query(JournalEntry).filter(
            JournalEntry.source == source,
            JournalEntry.reference_type == reference_type,
            JournalEntry.reference_id == reference_id,
            JournalEntry.is_reversal == False,  # noqa: E712
        ).all()
        if existing:
            for e in existing:
                if e.status == "posted":
                    return e
                raise AccountingError("Draft with the same posting key already exists")
        entry = self.create_draft(
            entry_date=entry_date, lines=lines, description=description,
            branch_id=branch_id, source=source,
            reference_type=reference_type, reference_id=reference_id,
            created_by=created_by,
        )
        return self.post_entry(entry.id, created_by)

    def reverse_entry(self, entry_id: str, user_id: str, reason: str | None = None) -> JournalEntry:
        original = self.db.query(JournalEntry).filter(JournalEntry.id == entry_id).first()
        if not original:
            raise AccountingError("Journal entry not found")
        if original.status != "posted":
            raise AccountingError("Only posted entries can be reversed")
        if original.is_reversal:
            raise AccountingError("Reversal entries cannot be reversed again")
        period = self.validate_period_open(original)
        lines = [
            {"account_id": ln.account_id, "debit": ln.credit, "credit": ln.debit,
             "branch_id": ln.branch_id, "production_batch_id": ln.production_batch_id,
             "passport_id": ln.passport_id, "cost_center": ln.cost_center}
            for ln in original.lines
        ]
        reversal = JournalEntry(
            entry_number=generate_entry_number(self.db),
            entry_date=original.entry_date,
            period_id=original.period_id,
            branch_id=original.branch_id,
            status="posted",
            source=original.source,
            description=f"Reversal of {original.entry_number}" + (f" - {reason}" if reason else ""),
            is_reversal=True,
            reversal_of_id=original.id,
            created_by=user_id,
            posted_by=user_id,
            posted_at=datetime.now(),
        )
        self.db.add(reversal)
        self.db.flush()
        for ln in lines:
            self.db.add(JournalLine(
                entry_id=reversal.id, account_id=ln["account_id"],
                debit=d(ln["debit"]), credit=d(ln["credit"]),
                branch_id=ln["branch_id"], production_batch_id=ln["production_batch_id"],
                passport_id=ln["passport_id"], cost_center=ln["cost_center"],
            ))
        self.db.flush()
        original.status = "reversed"
        self._apply_balances(reversal)
        self._audit("reverse_journal", reversal, user_id)
        self._audit("reverse_journal_original", original, user_id)
        self.db.flush()
        return reversal