src/tsuwave/database/models.py

"""SQLAlchemy models for TSU-WAVE"""

from sqlalchemy import (
    Column, String, Float, Boolean, DateTime, 
    Integer, JSON, ForeignKey, Index, text
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()

class Event(Base):
    """Tsunami event model"""
    
    __tablename__ = 'events'
    
    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    source_location = Column(String)
    magnitude = Column(Float)
    depth = Column(Float)  # source depth [m]
    time = Column(DateTime, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    active = Column(Boolean, default=True)
    metadata = Column(JSON, default={})
    
    # Relationships
    parameters = relationship("Parameter", back_populates="event")
    alerts = relationship("Alert", back_populates="event")
    
    # Indexes
    __table_args__ = (
        Index('idx_events_time', time.desc()),
        Index('idx_events_active', active),
    )

class Parameter(Base):
    """Seven parameters time series"""
    
    __tablename__ = 'parameters'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    time = Column(DateTime, nullable=False, default=datetime.utcnow)
    event_id = Column(String, ForeignKey('events.id'))
    zone = Column(String, nullable=False)
    
    # Seven parameters
    wcc = Column(Float)  # Wave Front Celerity Coefficient
    kpr = Column(Float)  # Kinetic/Potential Ratio
    hfsi = Column(Float) # Hydrodynamic Front Stability Index
    becf = Column(Float) # Bathymetric Energy Concentration Factor
    sdb = Column(Float)  # Spectral Dispersion Bandwidth
    sbsp = Column(Float) # Shoreline Boundary Stress Parameter
    smvi = Column(Float) # Sub-Surface Micro-Vorticity Index
    chi = Column(Float)  # Coastal Hazard Index
    
    # Relationships
    event = relationship("Event", back_populates="parameters")
    
    # Indexes
    __table_args__ = (
        Index('idx_parameters_time', time.desc()),
        Index('idx_parameters_zone_time', zone, time.desc()),
        Index('idx_parameters_event', event_id),
    )

class Alert(Base):
    """Alert model"""
    
    __tablename__ = 'alerts'
    
    id = Column(String, primary_key=True)
    event_id = Column(String, ForeignKey('events.id'))
    zone = Column(String, nullable=False)
    level = Column(String, nullable=False)  # LOW, MODERATE, HIGH, SEVERE, CATASTROPHIC
    chi = Column(Float)
    message = Column(String)
    issued_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime)
    acknowledged = Column(Boolean, default=False)
    acknowledged_by = Column(String)
    acknowledged_at = Column(DateTime)
    metadata = Column(JSON, default={})
    
    # Relationships
    event = relationship("Event", back_populates="alerts")
    
    # Indexes
    __table_args__ = (
        Index('idx_alerts_issued', issued_at.desc()),
        Index('idx_alerts_active', zone, level, 
              postgresql_where=text("acknowledged = FALSE")),
    )

class Zone(Base):
    """Coastal zone model with pre-computed BECF"""
    
    __tablename__ = 'zones'
    
    id = Column(String, primary_key=True)  # zone identifier
    name = Column(String, nullable=False)
    region = Column(String)  # pacific, indian, atlantic
    becf = Column(Float)  # pre-computed BECF value
    latitude = Column(Float)
    longitude = Column(Float)
    population = Column(Integer)
    priority = Column(String)  # low, medium, high, critical
    metadata = Column(JSON, default={})
    
    # Indexes
    __table_args__ = (
        Index('idx_zones_region', region),
        Index('idx_zones_becf', becf.desc()),
    )

class DARTStation(Base):
    """DART buoy station model"""
    
    __tablename__ = 'dart_stations'
    
    id = Column(String, primary_key=True)  # station ID
    name = Column(String)
    latitude = Column(Float, nullable=False)
    longitude = Column(Float, nullable=False)
    depth = Column(Float)  # water depth at station [m]
    active = Column(Boolean, default=True)
    last_seen = Column(DateTime)
    metadata = Column(JSON, default={})
    
    # Indexes
    __table_args__ = (
        Index('idx_dart_location', latitude, longitude),
    )

class ValidationEvent(Base):
    """Historical validation events"""
    
    __tablename__ = 'validation_events'
    
    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    year = Column(Integer)
    magnitude = Column(Float)
    max_runup = Column(Float)  # maximum observed run-up [m]
    mean_lead_time = Column(Integer)  # mean lead time [minutes]
    data_quality = Column(String)  # high, medium, low
    metadata = Column(JSON, default={})
    
    # Indexes
    __table_args__ = (
        Index('idx_validation_year', year.desc()),
    )

class User(Base):
    """User model for authentication"""
    
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, unique=True, nullable=False)
    email = Column(String, unique=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    full_name = Column(String)
    organization = Column(String)
    role = Column(String, default='viewer')  # viewer, operator, admin
    created_at = Column(DateTime, default=datetime.utcnow)
    last_login = Column(DateTime)
    active = Column(Boolean, default=True)
    metadata = Column(JSON, default={})
    
    # Indexes
    __table_args__ = (
        Index('idx_users_username', username),
        Index('idx_users_email', email),
    )

class ApiKey(Base):
    """API key model"""
    
    __tablename__ = 'api_keys'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String, unique=True, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    name = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime)
    last_used = Column(DateTime)
    rate_limit = Column(Integer, default=100)  # requests per minute
    active = Column(Boolean, default=True)
    
    # Indexes
    __table_args__ = (
        Index('idx_api_keys_key', key),
    )

# Create tables function
def create_tables(engine):
    """Create all tables"""
    Base.metadata.create_all(engine)

# Drop tables function
def drop_tables(engine):
    """Drop all tables"""
    Base.metadata.drop_all(engine)