from datetime import datetime
import uuid
from sqlalchemy import UUID, BigInteger, Boolean, Column, DateTime, Enum, Float, ForeignKey, Integer, String, UniqueConstraint, Index
from src.models.company_user import User
from sqlalchemy import UUID, BigInteger, Boolean, Column, DateTime, Enum, Float, ForeignKey, Integer, String, UniqueConstraint, NUMERIC
from src.models.company_user import User
from sqlalchemy import Column, String, Integer, Date, Numeric
from sqlalchemy.dialects.postgresql import JSONB, TIMESTAMP
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql import func

from src.core.database import Base
from sqlalchemy.orm import relationship




class ForecastData(Base):
    __tablename__ = "forecast_data"

    id = Column(Integer, primary_key=True, index=True)
    
    year = Column(Integer, nullable=False)
    quarter = Column(Integer, nullable=False)
    
    scenario = Column(String, nullable=False)         # e.g., "Baseline", "Best Case"
    metric_type = Column(String, nullable=False)      # e.g., "Revenue", "Profit", "COGS", "EBITDA", etc.
    
    is_ai = Column(Boolean, default=False)
    value = Column(Float, nullable=False)

    __table_args__ = (
        UniqueConstraint('year', 'quarter', 'scenario', 'is_ai', name='unique_year_quarter_scenario_is_ai'),
    )
#==============================================


class FinanceChartOfAccounts(Base):
    __tablename__ = "finance_chart_of_account"
    __table_args__ = {"schema": "fpna_landing"}

    account_id = Column(String(64), primary_key=True)
    account_code = Column(String(64), nullable=False)
    account_name = Column(String(255), nullable=False)
    account_type = Column(String(64), nullable=False)
    parent_account_id = Column(String(64))
    is_active = Column(Boolean, nullable=False)


class FCA(Base):
    __tablename__ = "finance_chart_of_accounts"
    __table_args__ = {"schema": "fpna_landing"}

    account_id = Column(String(64), primary_key=True)
    account_code = Column(String(64), nullable=False)
    account_name = Column(String(255), nullable=False)
    account_type = Column(String(64), nullable=False)
    parent_account_id = Column(String(64))
    is_active = Column(Boolean, nullable=False)


class GeneralLedgerTransaction(Base):
    __tablename__ = "finance_general_ledger_transactions"
    # Indexes added here are needed by the revenue-forecast GL aggregation
    # join (controller.py revenue_forecast). Without them, that join falls
    # back to a Seq Scan over millions of rows.
    __table_args__ = (
        Index("ix_fgl_gl_account",       "gl_account_id"),
        Index("ix_fgl_customer",         "customer_id"),
        Index("ix_fgl_period",           "period"),
        Index("ix_fgl_account_period",   "gl_account_id", "period"),
        {"schema": "fpna_landing"},
    )

    transaction_id = Column(BigInteger, primary_key=True, index=True)
    gl_account_id = Column(String(64), nullable=False)
    #date = Column(Date, nullable=False)
    transaction_date = Column(String, nullable=False)
    amount_debit = Column(Float, default=0.0)
    amount_credit = Column(Float, default=0.0)
    currency = Column(String, nullable=False)
    department_id = Column(String(64), nullable=False)
    cost_center_id = Column(String(64), nullable=True)
    profit_center_id = Column(BigInteger, index=True)
    entity_id = Column(BigInteger, index=True)
    customer_id = Column(String(64), nullable=True)
    vendor_id = Column(String(64), nullable=True)
    period = Column(String, nullable=False)


class TrialBalance(Base):
    __tablename__ = "finance_trial_balance"
    __table_args__ = {"schema": "fpna_landing"}  # adjust if needed

    period = Column(String(16), primary_key=True, index=True)
    gl_account_id = Column(String(64), primary_key=True, index=True)  # composite PK
    debit_total = Column(Numeric(18, 2), default=0.0)
    credit_total = Column(Numeric(18, 2), default=0.0)
    ending_balance = Column(Numeric(18, 2), default=0.0)
    entity_id = Column(String(64), index=True)


class Vendor(Base):
    __tablename__ = "finance_vendors"
    __table_args__ = {"schema": "fpna_landing"} 

    vendor_id = Column(Integer, primary_key=True, index=True)
    vendor_name = Column(String(255), nullable=False)
    category = Column(String(64), nullable=False)
    region = Column(String(64), nullable=True)
    payment_terms = Column(String(64), nullable=True)


class Customer(Base):
    __tablename__ = "finance_customers"
    __table_args__ = {"schema": "fpna_landing"} 

    customer_id = Column(String(64), primary_key=True, index=True, autoincrement=True)
    customer_name = Column(String(255), nullable=False)
    industry = Column(String(128), nullable=False)
    region = Column(String(64), nullable=False)
    size_segment = Column(String(64), nullable=False)



# ── Performance support tables ───────────────────────────────────────────────
# These two tables back the revenue-forecast performance pipeline:
#   - RevenueForecastCache: short-lived API response cache (L2, Postgres).
#   - RevenueForecastPrecomputed: persisted Prophet/ML forecast results so
#     the request path doesn't have to re-run the models per customer.
class RevenueForecastCache(Base):
    __tablename__ = "revenue_forecast_cache"
    __table_args__ = (
        Index("ix_rf_cache_expires", "expires_at"),
        {"schema": "fpna_config"},
    )

    cache_key  = Column(String(512), primary_key=True)
    payload    = Column(JSONB, nullable=False)
    expires_at = Column(TIMESTAMP(timezone=True), nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now())


class RevenueForecastPrecomputed(Base):
    __tablename__ = "revenue_forecast_precomputed"
    __table_args__ = (
        UniqueConstraint("company_id", "customer_name", "forecast_year", "period",
                         name="uq_rfp_company_customer_year_period"),
        Index("ix_rfp_company_year",          "company_id", "forecast_year"),
        Index("ix_rfp_company_year_customer", "company_id", "forecast_year", "customer_name"),
        {"schema": "fpna_config"},
    )

    id              = Column(BigInteger, primary_key=True, autoincrement=True)
    company_id      = Column(String(64),  nullable=False)
    customer_name   = Column(String(255), nullable=False)
    forecast_year   = Column(Integer,     nullable=False)
    period          = Column(String(7),   nullable=False)         # 'YYYY-MM'
    ai_amount       = Column(Numeric(18, 4))
    model_metadata  = Column(JSONB)
    computed_at     = Column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now())


from datetime import date
import pandas as pd
import joblib
from typing import Dict


def generate_ai_forecast(
    customer_name: str,
    start_date: date,
    end_date: date
) -> Dict[date, float]:
    """
    Load trained ML model and predict revenue per month.
    """
    model_path = f"models/{customer_name}.joblib"

    try:
        model = joblib.load(model_path)
    except FileNotFoundError:
        return {}

    future = pd.date_range(
        start=start_date,
        end=end_date,
        freq="MS"
    ).to_frame(index=False, name="ds")

    forecast = model.predict(future)

    return {
        row.ds.date(): max(0.0, float(row.yhat))
        for row in forecast.itertuples()
    }

