from sqlalchemy import Column, Integer, String, BigInteger, Boolean, Text, Date, DateTime, Numeric, Float, JSON, JSONB, ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql import func
import enum

Base = declarative_base()

# Enums for user-defined types
class ChartType(enum.Enum):
    # Define your chart types here
    BAR = "bar"
    LINE = "line"
    PIE = "pie"
    # Add other chart types as needed

class FontSize(enum.Enum):
    # Define font sizes here
    SMALL = "small"
    MEDIUM = "medium"
    LARGE = "large"
    # Add other sizes as needed

class UserRole(enum.Enum):
    # Define user roles here
    ADMIN = "admin"
    USER = "user"
    VIEWER = "viewer"
    # Add other roles as needed

class Department(enum.Enum):
    # Define departments here
    FINANCE = "finance"
    HR = "hr"
    SALES = "sales"
    MARKETING = "marketing"
    # Add other departments as needed

class PlanType(enum.Enum):
    # Define plan types here
    BASIC = "basic"
    PREMIUM = "premium"
    ENTERPRISE = "enterprise"
    # Add other plans as needed

class Priority(enum.Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    URGENT = "urgent"

class Status(enum.Enum):
    OPEN = "open"
    IN_PROGRESS = "in_progress"
    RESOLVED = "resolved"
    CLOSED = "closed"

# Cron schema models
class CronJob(Base):
    __tablename__ = 'job'
    __table_args__ = {'schema': 'cron'}
    
    jobid = Column(BigInteger, primary_key=True)
    schedule = Column(Text, nullable=False)
    command = Column(Text, nullable=False)
    nodename = Column(Text, nullable=False)
    nodeport = Column(Integer, nullable=False)
    database = Column(Text, nullable=False)
    username = Column(Text, nullable=False)
    active = Column(Boolean, nullable=False)
    jobname = Column(Text)

class CronJobRunDetails(Base):
    __tablename__ = 'job_run_details'
    __table_args__ = {'schema': 'cron'}
    
    runid = Column(BigInteger, primary_key=True)
    jobid = Column(BigInteger)
    job_pid = Column(Integer)
    database = Column(Text)
    username = Column(Text)
    command = Column(Text)
    status = Column(Text)
    return_message = Column(Text)
    start_time = Column(DateTime(timezone=True))
    end_time = Column(DateTime(timezone=True))

# fpna_insight_dev schema models
class DimAccount(Base):
    __tablename__ = 'dim_account'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    account_sk = Column(BigInteger, primary_key=True)
    account_id = Column(String(64), nullable=False)
    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))
    level_1 = Column(String(128))
    level_2 = Column(String(128))
    level_3 = Column(String(128))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimCostCenter(Base):
    __tablename__ = 'dim_cost_center'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    cost_center_sk = Column(BigInteger, primary_key=True)
    cost_center_id = Column(String(64), nullable=False)
    cost_center_name = Column(String(255), nullable=False)
    department_id = Column(String(64))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimCurrency(Base):
    __tablename__ = 'dim_currency'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    currency_sk = Column(BigInteger, primary_key=True)
    currency_code = Column(String(8), nullable=False)
    fx_to_base = Column(Numeric, nullable=False)
    fx_valid_from = Column(Date, nullable=False)
    fx_valid_to = Column(Date, nullable=False)

class DimCustomer(Base):
    __tablename__ = 'dim_customer'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    customer_sk = Column(BigInteger, primary_key=True)
    customer_id = Column(String(64), nullable=False)
    customer_name = Column(String(255), nullable=False)
    industry = Column(String(128))
    region = Column(String(64))
    size_segment = Column(String(64))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimDate(Base):
    __tablename__ = 'dim_date'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    date_key = Column(Integer, primary_key=True)
    dt = Column(Date, nullable=False)
    month_key = Column(Integer, nullable=False)
    month_start_dt = Column(Date, nullable=False)
    quarter = Column(String(6), nullable=False)
    year = Column(Integer, nullable=False)
    is_month_end = Column(Boolean, nullable=False)

class DimDepartment(Base):
    __tablename__ = 'dim_department'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    department_sk = Column(BigInteger, primary_key=True)
    department_id = Column(String(64), nullable=False)
    department_name = Column(String(255), nullable=False)
    manager_id = Column(String(64))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimEntity(Base):
    __tablename__ = 'dim_entity'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    entity_sk = Column(BigInteger, primary_key=True)
    entity_id = Column(String(64), nullable=False)
    entity_name = Column(String(255), nullable=False)
    region = Column(String(64))
    currency_code = Column(String(8))
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimProfitCenter(Base):
    __tablename__ = 'dim_profit_center'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    profit_center_sk = Column(BigInteger, primary_key=True)
    profit_center_id = Column(String(64), nullable=False)
    profit_center_name = Column(String(255), nullable=False)
    business_unit = Column(String(128))
    region = Column(String(64))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class DimVendor(Base):
    __tablename__ = 'dim_vendor'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    vendor_sk = Column(BigInteger, primary_key=True)
    vendor_id = Column(String(64), nullable=False)
    vendor_name = Column(String(255), nullable=False)
    category = Column(String(128))
    region = Column(String(64))
    payment_terms = Column(String(128))
    is_active = Column(Boolean, nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False)

class FactGLTransaction(Base):
    __tablename__ = 'fact_gl_transaction'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    gl_trx_sk = Column(BigInteger, primary_key=True)
    date_key = Column(Integer, nullable=False)
    account_sk = Column(BigInteger, nullable=False)
    entity_sk = Column(BigInteger)
    cost_center_sk = Column(BigInteger)
    profit_center_sk = Column(BigInteger)
    customer_sk = Column(BigInteger)
    vendor_sk = Column(BigInteger)
    currency_sk = Column(BigInteger)
    source_system = Column(String(64))
    amount_debit = Column(Numeric)
    amount_credit = Column(Numeric)
    amount_signed = Column(Numeric)
    fx_rate = Column(Numeric)
    amount_base = Column(Numeric)
    is_adjustment = Column(Boolean)
    is_intercompany = Column(Boolean)
    period_key = Column(Integer, nullable=False)

class FactTrialBalance(Base):
    __tablename__ = 'fact_trial_balance'
    __table_args__ = {'schema': 'fpna_insight_dev'}
    
    month_key = Column(Integer, primary_key=True)
    account_sk = Column(BigInteger, primary_key=True)
    entity_sk = Column(BigInteger, primary_key=True)
    debit_total = Column(Numeric)
    credit_total = Column(Numeric)
    ending_balance = Column(Numeric)
    ending_balance_base = Column(Numeric)

# # Public schema models
# class FinancialWidget(Base):
#     __tablename__ = 'Financial_widget'
    
#     id = Column(Integer, primary_key=True)
#     name = Column(String, nullable=False)
#     category = Column(String)
#     chart_Type = Column(ChartType, nullable=False)
#     enable_or_disable = Column(Boolean, default=True)
#     position = Column(Integer, nullable=False)
#     created_at = Column(DateTime(timezone=True), server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())

# class AppearanceThemeSettings(Base):
#     __tablename__ = 'appearance_theme_settings'
    
#     id = Column(BigInteger, primary_key=True)
#     primary_color = Column(String, nullable=False)
#     background_color = Column(String, nullable=False)
#     font_color = Column(String, nullable=False)
#     font_size = Column(FontSize, nullable=False)
#     font_family = Column(String, nullable=False)
#     company_id = Column(String, nullable=False)

# class ApplicationUsers(Base):
#     __tablename__ = 'application_users'
    
#     id = Column(String, primary_key=True)
#     email = Column(String, nullable=False)
#     hashed_password = Column(String, nullable=False)
#     full_name = Column(String, nullable=False)
#     role = Column(UserRole, nullable=False)
#     is_account_active = Column(Boolean, default=True)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())
#     is_2FA_required = Column(Boolean, nullable=False, default=False)
#     is_2FA_enabled = Column(Boolean, nullable=False, default=False)
#     temp_2fa_secret = Column(String(100))
#     secret_2fa = Column(String(100))
#     failed_login_attempts = Column(Integer, nullable=False, default=0)
#     is_account_locked = Column(Boolean, default=False)

# class Companies(Base):
#     __tablename__ = 'companies'
    
#     id = Column(String, primary_key=True)
#     company_name = Column(String(100), nullable=False)
#     legal_name = Column(String(150))
#     bio = Column(String(255))
#     address = Column(Text)
#     home_country = Column(String(100))
#     company_logo_url = Column(String(255))
#     has_branches = Column(Boolean, nullable=False, default=False)
#     users_count = Column(Integer, default=0)
#     email = Column(String(100), nullable=False)
#     phone_number = Column(String(20))
#     db_name = Column(String(100))
#     db_host = Column(String(100))
#     db_port = Column(String(6))
#     is_2FA_enabled = Column(Boolean, nullable=False, default=False)
#     plan = Column(PlanType, nullable=False)
#     trial_ends_at = Column(DateTime(timezone=True))
#     next_billing_date = Column(DateTime(timezone=True))
#     is_active = Column(Boolean, nullable=False, default=True)
#     settings = Column(JSON)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())
#     deleted_at = Column(DateTime(timezone=True))

# class CRM(Base):
#     __tablename__ = 'crm'
    
#     opportunity_id = Column(String, primary_key=True)
#     integration_id = Column(UUID)
#     customer_name = Column(String, nullable=False)
#     contact_person = Column(String, nullable=False)
#     email = Column(String, nullable=False)
#     product = Column(String, nullable=False)
#     deal_amount = Column(Float, nullable=False)
#     stage = Column(String, nullable=False)
#     probability = Column(Integer, nullable=False)
#     expected_close_date = Column(DateTime, nullable=False)
#     region = Column(String, nullable=False)
#     extra_data = Column(JSONB)
#     user_id = Column(String, nullable=False)
#     row_hash = Column(String(32))
#     created_at = Column(DateTime, nullable=False, server_default=func.now())

# class CRMIntegrationHistory(Base):
#     __tablename__ = 'crm_integration_history'
    
#     id = Column(UUID, primary_key=True)
#     source_type = Column(String(50), nullable=False)
#     source_identifier = Column(String(255))
#     status = Column(String(50), nullable=False)
#     message = Column(String(1000))
#     uploaded_user_id = Column(UUID)
#     created_at = Column(DateTime, nullable=False, server_default=func.now())

# class DerivedMetric(Base):
#     __tablename__ = 'derived_metric'
    
#     id = Column(Integer, primary_key=True)
#     name = Column(String(100), nullable=False)
#     formula = Column(String(500), nullable=False)
#     created_at = Column(DateTime(timezone=True), server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())

# class EmailNotificationSettings(Base):
#     __tablename__ = 'email_notification_settings'
    
#     id = Column(UUID, primary_key=True)
#     user_id = Column(String, nullable=False)
#     transaction_alert = Column(Boolean, default=True)
#     report_ready = Column(Boolean, default=True)
#     system_update = Column(Boolean, default=True)
#     weekly_summary = Column(Boolean, default=True)

# class Employees(Base):
#     __tablename__ = 'employees'
    
#     employee_id = Column(String, primary_key=True)
#     integration_id = Column(UUID)
#     name = Column(String, nullable=False)
#     email = Column(String, nullable=False)
#     job_title = Column(String, nullable=False)
#     department = Column(String, nullable=False)
#     start_date = Column(DateTime, nullable=False)
#     termination_date = Column(DateTime)
#     base_salary = Column(Float, nullable=False)
#     bonus = Column(Float)
#     status = Column(String, nullable=False)
#     location = Column(String)
#     extra_data = Column(JSONB)
#     user_id = Column(String, nullable=False)
#     row_hash = Column(String(32))
#     created_at = Column(DateTime, server_default=func.now())

# class FileUploadHistory(Base):
#     __tablename__ = 'file_upload_history'
    
#     id = Column(Integer, primary_key=True)
#     file_name = Column(String(255), nullable=False)
#     file_path = Column(String(512), nullable=False)
#     file_type = Column(String(50))
#     uploaded_by = Column(String(255), nullable=False)
#     uploaded_at = Column(DateTime, server_default=func.now())
#     status = Column(String(50))

# class GeneralLedger(Base):
#     __tablename__ = 'general_ledger'
    
#     id = Column(UUID, primary_key=True)
#     date = Column(DateTime, nullable=False)
#     general_ledger_code = Column(Numeric, nullable=False)
#     account_name = Column(String(255), nullable=False)
#     category = Column(String(50))
#     description = Column(String(500))
#     debit = Column(Numeric, nullable=False)
#     credit = Column(Numeric, nullable=False)
#     is_active = Column(Boolean, nullable=False, default=True)
#     entry_by = Column(String(255), nullable=False)
#     entry_system_source = Column(String(255), nullable=False)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), nullable=False, onupdate=func.now())
#     transaction_hash = Column(String)
#     integration_id = Column(UUID)
#     extra_data = Column(JSONB)

# class GeneralLedgerMaster(Base):
#     __tablename__ = 'general_ledger_master'
    
#     id = Column(UUID, primary_key=True)
#     general_ledger_code = Column(Numeric, nullable=False)
#     account_name = Column(String(255), nullable=False)
#     category = Column(String(50))
#     tags = Column(ARRAY(String))
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), nullable=False, onupdate=func.now())

# class IntegrationHistory(Base):
#     __tablename__ = 'integration_history'
    
#     id = Column(UUID, primary_key=True)
#     source_type = Column(String(50), nullable=False)
#     source_identifier = Column(String(255))
#     status = Column(String(50), nullable=False)
#     message = Column(String(1000))
#     uploaded_user_id = Column(UUID)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

# class MetricDefinition(Base):
#     __tablename__ = 'metric_definition'
    
#     id = Column(Integer, primary_key=True)
#     name = Column(String(100), nullable=False)
#     tags = Column(ARRAY(String))
#     gl_codes = Column(ARRAY(String))
#     amount_field = Column(String(10))
#     created_at = Column(DateTime(timezone=True), server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())

# class Tickets(Base):
#     __tablename__ = 'tickets'
    
#     id = Column(Integer, primary_key=True)
#     ticket_id = Column(String)
#     company_id = Column(String, nullable=False)
#     user_id = Column(String, nullable=False)
#     email = Column(String, nullable=False)
#     issue_type = Column(String, nullable=False)
#     title = Column(String, nullable=False)
#     description = Column(String, nullable=False)
#     priority = Column(Priority, nullable=False)
#     status = Column(Status, default=Status.OPEN)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), nullable=False, onupdate=func.now())

# class Attachments(Base):
#     __tablename__ = 'attachments'
    
#     id = Column(Integer, primary_key=True)
#     ticket_id = Column(Integer, nullable=False)
#     file_path = Column(String, nullable=False)
#     uploaded_at = Column(DateTime, nullable=False, server_default=func.now())

# class Users(Base):
#     __tablename__ = 'users'
    
#     id = Column(String, primary_key=True)
#     company_name = Column(String(100), nullable=False)
#     company_id = Column(String, nullable=False)
#     full_name = Column(String(100), nullable=False)
#     email = Column(String(100), nullable=False)
#     hashed_password = Column(String(255), nullable=False)
#     is_2FA_required = Column(Boolean, nullable=False, default=False)
#     is_2FA_enabled = Column(Boolean, nullable=False, default=False)
#     temp_2fa_secret = Column(String(100))
#     secret_2fa = Column(String(100))
#     department = Column(Department, nullable=False)
#     role = Column(UserRole, nullable=False)
#     permissions = Column(JSON)
#     system_password_reset = Column(Boolean, nullable=False, default=False)
#     password_reset_token = Column(String(512))
#     is_active = Column(Boolean, nullable=False, default=True)
#     created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
#     updated_at = Column(DateTime(timezone=True), onupdate=func.now())
#     deleted_at = Column(DateTime(timezone=True))

