from datetime import datetime
from sqlalchemy import Column, Integer, String, Boolean, ForeignKey, DateTime, Enum, Text, UniqueConstraint
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
import enum
from app.core.database import Base

class UserRole(str, enum.Enum):
    SUPER_ADMIN = "super_admin"
    ADMIN_CS = "admin_cs"
    TEKNISI = "teknisi"
    AGEN_VOUCHER = "agen_voucher"

class CustomerType(str, enum.Enum):
    BULANAN_PPP = "bulanan_ppp"
    HOTSPOT_VOUCHER = "hotspot_voucher"

class ServicePackage(Base):
    """Admin-maintained commercial/service options shown in installation forms."""
    __tablename__ = "service_packages"
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String, unique=True, nullable=False, index=True)
    name = Column(String, nullable=False)
    service_type = Column(String, nullable=False, default="bulanan_ppp")
    service_mode = Column(String, nullable=False, default="private_pppoe")
    price = Column(Integer, nullable=False, default=0)
    mikrotik_profile = Column(String)
    olt_vlan_profile = Column(String, default="bnbv1009")
    service_vlan = Column(Integer, default=175)
    pppoe_vlan = Column(Integer, default=100)
    hotspot_vlan = Column(Integer, default=150)
    iptv_vlan = Column(Integer, default=200)
    service_profile_id = Column(Integer, ForeignKey("service_profiles.id"), nullable=True)
    is_active = Column(Boolean, default=True)
    service_profile = relationship("ServiceProfile", foreign_keys=[service_profile_id])

class CustomerStatus(str, enum.Enum):
    REQUEST = "request"
    INSTALLING = "installing"
    CONFIGURING = "configuring"
    ACTIVE = "active"
    SUSPENDED = "suspended"

class ServiceMode(str, enum.Enum):
    HOTSPOT = "hotspot"
    PRIVATE_PPPOE = "private_pppoe"

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    full_name = Column(String)
    hashed_password = Column(String)
    role = Column(String, default=UserRole.TEKNISI)
    is_active = Column(Boolean, default=True)

class MikrotikConfig(Base):
    __tablename__ = "mikrotik_configs"
    id = Column(Integer, primary_key=True)
    host = Column(String)
    username = Column(String)
    password = Column(String)
    api_port = Column(Integer, default=8728)

class Customer(Base):
    __tablename__ = "customers"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    customer_code = Column(String, unique=True, index=True, nullable=True)
    nik = Column(String, unique=True, index=True, nullable=True)
    address = Column(Text)
    phone = Column(String)
    
    type = Column(String)
    play_type = Column(String, default="1play")
    status = Column(String, default=CustomerStatus.REQUEST)
    
    # Billing
    harga_paket = Column(Integer, default=0)
    service_package_id = Column(Integer, ForeignKey("service_packages.id"), nullable=True)
    service_profile_id = Column(Integer, ForeignKey("service_profiles.id"), nullable=True)
    is_paid = Column(Boolean, default=False)
    is_archived = Column(Boolean, default=False)
    jatuh_tempo = Column(DateTime(timezone=True), nullable=True)
    
    odp_info = Column(String)
    ont_sn = Column(String)
    ont_brand = Column(String)
    ont_rx_power = Column(String)
    ont_tx_power = Column(String)
    # Device assignments are explicit. Vendor/model are snapshots retained for audit.
    olt_device_id = Column(Integer, ForeignKey("olt_devices.id"), nullable=True)
    mikrotik_device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    olt_vendor = Column(String, default="ZTE")
    olt_model = Column(String, default="C320")
    olt_adapter_key = Column(String)
    olt_fields_json = Column(Text)  # non-secret, adapter-defined provisioning fields
    olt_port = Column(String)
    ont_sn_mode = Column(String, default="manual")
    onu_id_mode = Column(String, default="manual")
    olt_port_mode = Column(String, default="manual")
    ont_brand_mode = Column(String, default="manual")
    hardware_discovery_status = Column(String, default="unavailable")
    odp_latitude = Column(String)
    odp_longitude = Column(String)
    customer_latitude = Column(String)
    customer_longitude = Column(String)
    wifi_ssid_24 = Column(String)
    wifi_password_24_enc = Column(String)
    wifi_ssid_5 = Column(String)
    wifi_password_5_enc = Column(String)
    wifi_ssid_hotspot = Column(String)
    wifi_password_hotspot_enc = Column(String)
    acs_wifi_status = Column(String, default="not_configured")
    acs_last_sync = Column(DateTime(timezone=True))
    
    mikrotik_user = Column(String)
    mikrotik_pass = Column(String)
    pppoe_username = Column(String)
    pppoe_password = Column(String)  # encrypted at rest
    pppoe_secret_revealed_at = Column(DateTime(timezone=True), nullable=True)
    pppoe_secret_version = Column(Integer, default=1)
    service_mode = Column(String, default=ServiceMode.PRIVATE_PPPOE.value)
    management_pppoe_username = Column(String)
    management_pppoe_password = Column(String)
    management_pppoe_vlan = Column(Integer, nullable=True)
    service_profile = Column(String)
    provisioning_mode = Column(String, default="private_pppoe")
    service_modes_json = Column(Text, default='["pppoe"]')
    ont_management_mode = Column(String, default="none")
    onu_id = Column(Integer)
    wan_vlan = Column(Integer, default=175)
    service_vlan = Column(Integer, default=175)
    pppoe_vlan = Column(Integer, default=100)
    hotspot_vlan = Column(Integer, default=150)
    iptv_vlan = Column(Integer, default=200)
    olt_vlan_profile = Column(String, default="bnbv1009")
    vlan_id = Column(Integer)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    assigned_teknisi_id = Column(Integer, ForeignKey("users.id"))
    odp_id = Column(Integer, ForeignKey("odps.id"), nullable=True)
    odp_port = Column(Integer, nullable=True)
    assigned_teknisi = relationship("User", foreign_keys=[assigned_teknisi_id])
    odp = relationship("ODP", foreign_keys=[odp_id])
    olt_device = relationship("OLTDevice", foreign_keys=[olt_device_id])
    mikrotik_device = relationship("MikrotikDevice", foreign_keys=[mikrotik_device_id])
    service_package = relationship("ServicePackage", foreign_keys=[service_package_id])
    service_catalog_profile = relationship("ServiceProfile", foreign_keys=[service_profile_id])

    # Provisioning state: one form feeds MikroTik and OLT independently.
    provisioning_status = Column(String, default="not_started")
    mikrotik_provisioning_status = Column(String, default="pending")
    olt_provisioning_status = Column(String, default="pending")
    provisioning_message = Column(Text)
    provisioned_at = Column(DateTime(timezone=True), nullable=True)

class VoucherAgent(Base):
    __tablename__ = "voucher_agents"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    phone = Column(String)
    area = Column(String, index=True)
    commission_rate = Column(Integer, default=0)
    code = Column(String, nullable=True)
    comment = Column(String, nullable=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, nullable=True)

class Voucher(Base):
    __tablename__ = "vouchers"
    id = Column(Integer, primary_key=True, index=True)
    # Legacy/public serial. Existing vouchers use this as username/password.
    code = Column(String, unique=True, index=True, nullable=False)
    username = Column(String, nullable=True, index=True)
    password_ciphertext = Column(Text, nullable=True)
    credential_mode = Column(String, default="legacy_same")
    credential_length = Column(Integer, nullable=True)
    character_mode = Column(String, nullable=True)
    profile = Column(String, nullable=False)
    profile_id = Column(Integer, ForeignKey("hotspot_profiles.id"), nullable=True)
    price = Column(Integer, default=0)
    status = Column(String, default="available")
    agent_id = Column(Integer, ForeignKey("voucher_agents.id"), nullable=True)
    comment = Column(String, nullable=True)
    limit_uptime = Column(String, nullable=True)
    limit_idle_time = Column(String, nullable=True)
    sold_at = Column(DateTime(timezone=True), nullable=True)
    used_at = Column(DateTime(timezone=True), nullable=True)
    expires_at = Column(DateTime(timezone=True), nullable=True)
    sync_status = Column(String, default="pending")
    sync_updated_at = Column(DateTime, nullable=True)
    batch_id = Column(Integer, ForeignKey("voucher_batches.id"), nullable=True)
    device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    created_at = Column(DateTime, default=datetime.now)
    created_by = Column(Integer, ForeignKey("users.id"), nullable=True)
    sold_price = Column(Integer, nullable=True)
    agent = relationship("VoucherAgent", foreign_keys=[agent_id])
    batch = relationship("VoucherBatch", foreign_keys=[batch_id], back_populates="vouchers")
    hotspot_profile = relationship("HotspotProfile", foreign_keys=[profile_id])

class Payment(Base):
    __tablename__ = "payments"
    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False, index=True)
    amount = Column(Integer, nullable=False)
    period = Column(String, nullable=False)
    method = Column(String, default="cash")
    status = Column(String, default="paid")
    reference = Column(String)
    paid_at = Column(DateTime(timezone=True), server_default=func.now())
    voucher_id = Column(Integer, ForeignKey("vouchers.id"), nullable=True)
    reference_type = Column(String, nullable=True)
    device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    customer = relationship("Customer", foreign_keys=[customer_id])

class Invoice(Base):
    __tablename__ = "invoices"
    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False, index=True)
    period = Column(String, nullable=False, index=True)
    amount = Column(Integer, nullable=False)
    due_date = Column(DateTime(timezone=True), nullable=False)
    status = Column(String, default="unpaid", index=True)
    paid_at = Column(DateTime(timezone=True), nullable=True)
    customer = relationship("Customer", foreign_keys=[customer_id])

class DeviceCredential(Base):
    """Encrypted login material referenced by a device; plaintext is never stored."""
    __tablename__ = "device_credentials"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False, index=True)
    protocol = Column(String, nullable=False)
    username = Column(String, nullable=False)
    encrypted_secret = Column(Text, nullable=False)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    cred_type = Column(String, nullable=True)


class MikrotikDevice(Base):
    __tablename__ = "mikrotik_devices"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    host = Column(String, nullable=False)
    api_port = Column(Integer, default=8728)
    area = Column(String, index=True)
    status = Column(String, default="registered")
    is_active = Column(Boolean, default=True)
    credential_id = Column(Integer, ForeignKey("device_credentials.id"), nullable=True)
    hotspot_name = Column(String, nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    last_sync_at = Column(DateTime, nullable=True)
    offline_mode = Column(Boolean, default=False)
    credential = relationship("DeviceCredential", foreign_keys=[credential_id])

class OLTDevice(Base):
    __tablename__ = "olt_devices"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    host = Column(String, nullable=False)
    vendor = Column(String, nullable=False)
    model = Column(String, nullable=False)
    location = Column(String)
    pop_name = Column(String)
    management_protocol = Column(String, default="ssh")
    api_port = Column(Integer)
    status = Column(String, default="registered")
    is_active = Column(Boolean, default=True)
    credential_id = Column(Integer, ForeignKey("device_credentials.id"), nullable=True)
    hotspot_name = Column(String, nullable=True)
    credential = relationship("DeviceCredential", foreign_keys=[credential_id])

class ODP(Base):
    __tablename__ = "odps"
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String, unique=True, nullable=False)
    name = Column(String, nullable=False)
    latitude = Column(String)
    longitude = Column(String)
    port_count = Column(Integer, default=8)
    mikrotik_device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    mikrotik_device = relationship("MikrotikDevice", foreign_keys=[mikrotik_device_id])
    is_active = Column(Boolean, default=True)

class ServiceProfile(Base):
    """Admin-managed technical profile used by installation forms."""
    __tablename__ = "service_profiles"
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String, unique=True, nullable=False, index=True)
    name = Column(String, nullable=False)
    olt_vlan_profile = Column(String, nullable=True)
    download_kbps = Column(Integer, nullable=True)
    upload_kbps = Column(Integer, nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)

class VlanMappingRule(Base):
    __tablename__ = "vlan_mapping_rules"
    id = Column(Integer, primary_key=True, index=True)
    source_vlan = Column(Integer, nullable=False, index=True)
    target_vlan = Column(Integer, nullable=False)
    purpose = Column(String, nullable=False, default="service")
    is_active = Column(Boolean, nullable=False, default=True)
    __table_args__ = (UniqueConstraint("source_vlan", "purpose", name="uq_vlan_mapping_source_purpose"),)

class AuditLog(Base):
    __tablename__ = "audit_logs"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    action = Column(String, nullable=False)
    entity = Column(String)
    entity_id = Column(Integer)
    detail = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    user = relationship("User", foreign_keys=[user_id])

class ProvisioningJob(Base):
    __tablename__ = "provisioning_jobs"
    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False, index=True)
    target = Column(String, nullable=False)  # mikrotik or olt
    status = Column(String, default="queued", index=True)
    vendor = Column(String)
    adapter_key = Column(String)
    olt_device_id = Column(Integer, ForeignKey("olt_devices.id"), nullable=True)
    mikrotik_device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    payload_summary = Column(Text)  # sanitized metadata, never secrets/commands
    attempts = Column(Integer, default=0)
    last_error = Column(Text)
    executed_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    customer = relationship("Customer", foreign_keys=[customer_id])

class ProvisioningEvent(Base):
    """Immutable, sanitized audit trail for preview, queue, and device acknowledgement."""
    __tablename__ = "provisioning_events"
    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False, index=True)
    target = Column(String, nullable=False)
    status = Column(String, nullable=False, index=True)
    vendor = Column(String)
    olt_device_id = Column(Integer, ForeignKey("olt_devices.id"), nullable=True)
    mikrotik_device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    adapter_key = Column(String)
    payload_summary = Column(Text)
    message = Column(Text)
    executed_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    customer = relationship("Customer", foreign_keys=[customer_id])

# Phase 2 models
import enum

class EntityStatus(str, enum.Enum):
    available = "available"
    sold = "sold"
    used = "used"
    expired = "expired"

class Site(Base):
    __tablename__ = "sites"
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String, unique=True, nullable=False)
    name = Column(String, nullable=False)
    area = Column(String)
    location = Column(Text)
    timezone = Column(String, default="Asia/Jakarta")
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)
    # One-directional: Site -> MikroTik devices
    devices = relationship("MikrotikDevice", foreign_keys="MikrotikDevice.site_id")

class VoucherBatch(Base):
    __tablename__ = "voucher_batches"
    id = Column(Integer, primary_key=True, index=True)
    batch_code = Column(String, unique=True, nullable=False)
    profile = Column(String, nullable=False)
    profile_id = Column(Integer, ForeignKey("hotspot_profiles.id"), nullable=True)
    quantity = Column(Integer, nullable=False)
    generated_count = Column(Integer, default=0)
    price = Column(Integer, nullable=False)
    duration_days = Column(Integer, nullable=True)
    limit_uptime = Column(String, nullable=True)
    limit_idle_time = Column(String, nullable=True)
    user_mode = Column(String, nullable=True)
    character_length = Column(Integer, nullable=True)
    character_mode = Column(String, nullable=True)
    status = Column(String, default="generated")
    agent_id = Column(Integer, ForeignKey("voucher_agents.id"), nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    created_by = Column(Integer, ForeignKey("users.id"), nullable=True)
    comment = Column(Text)
    sync_status = Column(String, default="pending")
    created_at = Column(DateTime, default=datetime.now)
    vouchers = relationship("Voucher", foreign_keys="Voucher.batch_id", back_populates="batch")
    hotspot_profile = relationship("HotspotProfile", foreign_keys=[profile_id])

class HotspotProfile(Base):
    __tablename__ = "hotspot_profiles"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    local_profile = Column(String, nullable=True)
    rate_down = Column(Integer, nullable=True)
    rate_up = Column(Integer, nullable=True)
    session_timeout = Column(String, default="12h")
    idle_timeout = Column(String, nullable=True)
    shared = Column(Integer, default=1)
    price = Column(Integer, default=0)
    # Database validity/expiry; distinct from RouterOS session_timeout.
    voucher_expiry_days = Column(Integer, default=30)
    is_active = Column(Boolean, default=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    sync_status = Column(String, default="pending")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, nullable=True)

class AgentCommission(Base):
    __tablename__ = "agent_commissions"
    id = Column(Integer, primary_key=True, index=True)
    agent_id = Column(Integer, ForeignKey("voucher_agents.id"), nullable=False)
    voucher_id = Column(Integer, ForeignKey("vouchers.id"), nullable=True)
    batch_id = Column(Integer, ForeignKey("voucher_batches.id"), nullable=True)
    amount = Column(Integer, nullable=False)
    rate = Column(Integer, nullable=False)
    voucher_price = Column(Integer, nullable=False)
    transaction_id = Column(Integer, ForeignKey("payments.id"), nullable=True)
    period = Column(String)
    status = Column(String, default="pending")
    created_at = Column(DateTime, default=datetime.now)

class WalletTransaction(Base):
    __tablename__ = "wallet_transactions"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    voucher_id = Column(Integer, ForeignKey("vouchers.id"), nullable=True)
    amount = Column(Integer, nullable=False)
    method = Column(String, default="wallet")
    wallet_type = Column(String)  # dana, ovo, gopay, etc.
    status = Column(String, nullable=False)
    reference = Column(String)
    used_at = Column(DateTime, nullable=True)
    device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    created_at = Column(DateTime, default=datetime.now)

class RadiusServer(Base):
    __tablename__ = "radius_servers"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    host = Column(String, nullable=False)
    port = Column(Integer, default=1812)
    secret = Column(String, nullable=False)
    is_primary = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    created_at = Column(DateTime, default=datetime.now)

class SyncQueue(Base):
    __tablename__ = "sync_queue"
    id = Column(Integer, primary_key=True, index=True)
    entity_type = Column(String, nullable=False)
    entity_id = Column(Integer, nullable=False)
    target_site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    target_device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    status = Column(String, default="pending")
    attempts = Column(Integer, default=0)
    last_error = Column(Text)
    payload = Column(Text)
    created_at = Column(DateTime, default=datetime.now)
    synced_at = Column(DateTime, nullable=True)

class OfflineLog(Base):
    __tablename__ = "offline_logs"
    id = Column(Integer, primary_key=True, index=True)
    device_id = Column(Integer, ForeignKey("mikrotik_devices.id"), nullable=True)
    site_id = Column(Integer, ForeignKey("sites.id"), nullable=True)
    entity_type = Column(String, nullable=False)
    entity_id = Column(Integer, nullable=False)
    action = Column(String, nullable=False)
    payload = Column(Text)
    status = Column(String, default="pending")
    created_at = Column(DateTime, default=datetime.now)