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)
    # Kolom ini sudah ada di SQLite (voucher_agents), tapi belum pernah
    # dideklarasikan di model ORM sehingga db.add() melempar TypeError.
    nama_isp = Column(String, nullable=True)
    domain_isp = Column(String, nullable=True)
    prefix = Column(String, nullable=True)
    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)
    # Cumulative consumed seconds across ALL sessions. RouterOS `uptime` resets
    # to 0 on every logout, so it alone cannot represent total usage. This
    # accumulator is what makes the quota condition authoritative on our side.
    used_seconds = Column(Integer, default=0, nullable=False)
    # Last observed `uptime` of the current session; used to compute the delta.
    last_poll_uptime = Column(Integer, default=0, nullable=False)
    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)
    # Precise validity in seconds. Preserves minute/hour granularity chosen in
    # the profile form, which duration_days alone cannot express.
    duration_seconds = 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)
    voucher_expiry_seconds = Column(Integer, nullable=True)
    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 VoucherArchive(Base):
    __tablename__ = "voucher_archives"
    id = Column(Integer, primary_key=True, index=True)
    voucher_id = Column(Integer)
    code = Column(String)
    username = Column(String)
    profile = Column(String)
    price = Column(Integer)
    status = Column(String)
    sold_at = Column(DateTime)
    used_at = Column(DateTime)
    # Total seconds actually consumed before removal (quota accounting).
    used_seconds = Column(Integer, default=0)
    deleted_at = Column(DateTime, default=datetime.now)
    reason = Column(String) # expired, duration_exhausted, gone_from_device
    batch_id = Column(Integer)
    agent_id = Column(Integer)
    created_at = Column(DateTime, default=datetime.now)

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)


class OnlineOrder(Base):
    """
    Pesanan voucher dari katalog publik. Berbeda dari Invoice, tabel ini
    tidak terkait pelanggan existing: pembeli boleh orang luar sistem.

    Lifecycle status:
        pending  → order dibuat, voucher belum direservasi (checkout masih jalan)
        reserved → voucher sudah dipegang order ini, menunggu pembayaran
        paid     → Midtrans mengonfirmasi pembayaran
        delivered→ voucher sudah diserahkan ke pembeli
        expired  → batas bayar habis, voucher dilepas kembali ke stok
        cancelled→ dibatalkan manual oleh admin

    Aturan penting: hanya `reserved` yang memegang voucher. Menjalankan
    expired harus mengembalikan voucher ke status 'available' supaya stok
    tidak berkurang permanen.
    """
    __tablename__ = "online_orders"
    id = Column(Integer, primary_key=True, index=True)
    order_code = Column(String, unique=True, nullable=False, index=True)
    customer_name = Column(String, nullable=False)
    customer_phone = Column(String, nullable=False)
    customer_email = Column(String, nullable=True)
    # Simpan harga saat transaksi, bukan referensi ke profile. Kalau harga
    # paket berubah Tomorrow, order lama harus tetap menampilkan harga asli.
    profile_name = Column(String, nullable=True)
    profile_id = Column(Integer, ForeignKey("hotspot_profiles.id"), nullable=True)
    duration_label = Column(String, nullable=True)
    quantity = Column(Integer, default=1, nullable=False)
    total_price = Column(Integer, nullable=False)
    status = Column(String, default="pending", nullable=False, index=True)
    # JSON-encoded list of voucher.id yang dipegang order ini.
    reserved_voucher_ids = Column(Text, nullable=True)
    midtrans_order_id = Column(String, nullable=True)
    snap_token = Column(String, nullable=True)
    payment_method = Column(String, nullable=True)
    # Diisi saat pembayaran terkonfirmasi (dari Midtrans webhook).
    paid_at = Column(DateTime(timezone=True), nullable=True)
    delivered_at = Column(DateTime(timezone=True), nullable=True)
    expires_at = Column(DateTime(timezone=True), nullable=True)
    admin_note = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.now, nullable=False)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)