terra-view/backend/models.py

from sqlalchemy import Column, String, DateTime, Boolean, Text, Date, Integer, UniqueConstraint
from datetime import datetime
from backend.database import Base


class Emitter(Base):
    __tablename__ = "emitters"

    id = Column(String, primary_key=True, index=True)
    unit_type = Column(String, nullable=False)
    last_seen = Column(DateTime, default=datetime.utcnow)
    last_file = Column(String, nullable=False)
    status = Column(String, nullable=False)
    notes = Column(String, nullable=True)


class RosterUnit(Base):
    """
    Roster table: represents our *intended assignment* of a unit.
    This is editable from the GUI.

    Supports multiple device types with type-specific fields:
    - "seismograph" - Seismic monitoring devices (default)
    - "modem" - Field modems and network equipment
    - "slm" - Sound level meters (NL-43/NL-53)
    """
    __tablename__ = "roster"

    # Core fields (all device types)
    id = Column(String, primary_key=True, index=True)
    unit_type = Column(String, default="series3")  # Backward compatibility
    device_type = Column(String, default="seismograph")  # "seismograph" | "modem" | "slm"
    deployed = Column(Boolean, default=True)
    retired = Column(Boolean, default=False)
    out_for_calibration = Column(Boolean, default=False)
    allocated = Column(Boolean, default=False)  # Staged for an upcoming job, not yet deployed
    allocated_to_project_id = Column(String, nullable=True)  # Which project it's allocated to
    note = Column(String, nullable=True)
    project_id = Column(String, nullable=True)
    location = Column(String, nullable=True)  # Legacy field - use address/coordinates instead
    address = Column(String, nullable=True)  # Human-readable address
    coordinates = Column(String, nullable=True)  # Lat,Lon format: "34.0522,-118.2437"
    last_updated = Column(DateTime, default=datetime.utcnow)

    # Seismograph-specific fields (nullable for modems and SLMs)
    last_calibrated = Column(Date, nullable=True)
    next_calibration_due = Column(Date, nullable=True)

    # Modem assignment (shared by seismographs and SLMs)
    deployed_with_modem_id = Column(String, nullable=True)  # FK to another RosterUnit (device_type=modem)

    # Modem-specific fields (nullable for seismographs and SLMs)
    ip_address = Column(String, nullable=True)
    phone_number = Column(String, nullable=True)
    hardware_model = Column(String, nullable=True)
    deployment_type = Column(String, nullable=True)  # "seismograph" | "slm" - what type of device this modem is deployed with
    deployed_with_unit_id = Column(String, nullable=True)  # ID of seismograph/SLM this modem is deployed with

    # Sound Level Meter-specific fields (nullable for seismographs and modems)
    slm_host = Column(String, nullable=True)  # Device IP or hostname
    slm_tcp_port = Column(Integer, nullable=True)  # TCP control port (default 2255)
    slm_ftp_port = Column(Integer, nullable=True)  # FTP data retrieval port (default 21)
    slm_model = Column(String, nullable=True)  # NL-43, NL-53, etc.
    slm_serial_number = Column(String, nullable=True)  # Device serial number
    slm_frequency_weighting = Column(String, nullable=True)  # A, C, Z
    slm_time_weighting = Column(String, nullable=True)  # F (Fast), S (Slow), I (Impulse)
    slm_measurement_range = Column(String, nullable=True)  # e.g., "30-130 dB"
    slm_last_check = Column(DateTime, nullable=True)  # Last communication check


class WatcherAgent(Base):
    """
    Watcher agents: tracks the watcher processes (series3-watcher, thor-watcher)
    that run on field machines and report unit heartbeats.

    Updated on every heartbeat received from each source_id.
    """
    __tablename__ = "watcher_agents"

    id = Column(String, primary_key=True, index=True)        # source_id (hostname)
    source_type = Column(String, nullable=False)              # series3_watcher | series4_watcher
    version = Column(String, nullable=True)                   # e.g. "1.4.0"
    last_seen = Column(DateTime, default=datetime.utcnow)
    status = Column(String, nullable=False, default="unknown") # ok | pending | missing | error | unknown
    ip_address = Column(String, nullable=True)
    log_tail = Column(Text, nullable=True)                    # last N log lines (JSON array of strings)
    update_pending = Column(Boolean, default=False)           # set True to trigger remote update
    update_version = Column(String, nullable=True)            # target version to update to


class IgnoredUnit(Base):
    """
    Ignored units: units that report but should be filtered out from unknown emitters.
    Used to suppress noise from old projects.
    """
    __tablename__ = "ignored_units"

    id = Column(String, primary_key=True, index=True)
    reason = Column(String, nullable=True)
    ignored_at = Column(DateTime, default=datetime.utcnow)


class UnitHistory(Base):
    """
    Unit history: complete timeline of changes to each unit.
    Tracks note changes, status changes, deployment/benched events, and more.
    """
    __tablename__ = "unit_history"

    id = Column(Integer, primary_key=True, autoincrement=True)
    unit_id = Column(String, nullable=False, index=True)  # FK to RosterUnit.id
    change_type = Column(String, nullable=False)  # note_change, deployed_change, retired_change, etc.
    field_name = Column(String, nullable=True)  # Which field changed
    old_value = Column(Text, nullable=True)  # Previous value
    new_value = Column(Text, nullable=True)  # New value
    changed_at = Column(DateTime, default=datetime.utcnow, nullable=False, index=True)
    source = Column(String, default="manual")  # manual, csv_import, telemetry, offline_sync
    notes = Column(Text, nullable=True)  # Optional reason/context for the change


class UserPreferences(Base):
    """
    User preferences: persistent storage for application settings.
    Single-row table (id=1) to store global user preferences.
    """
    __tablename__ = "user_preferences"

    id = Column(Integer, primary_key=True, default=1)
    timezone = Column(String, default="America/New_York")
    theme = Column(String, default="auto")  # auto, light, dark
    auto_refresh_interval = Column(Integer, default=10)  # seconds
    date_format = Column(String, default="MM/DD/YYYY")
    table_rows_per_page = Column(Integer, default=25)
    calibration_interval_days = Column(Integer, default=365)
    calibration_warning_days = Column(Integer, default=30)
    status_ok_threshold_hours = Column(Integer, default=12)
    status_pending_threshold_hours = Column(Integer, default=24)
    # Mic display units on the event-report waveform chart only — peaks
    # and KPI tiles elsewhere are always dBL.  "psi" (default — matches
    # the PDF report) or "dBL".  Default flipped in v0.13.1 after
    # operator feedback that the chart should mirror the PDF.
    mic_unit_pref = Column(String, default="psi")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


# ============================================================================
# Project Management System
# ============================================================================

class ProjectType(Base):
    """
    Project type templates: defines available project types and their capabilities.
    Pre-populated with: sound_monitoring, vibration_monitoring, combined.
    """
    __tablename__ = "project_types"

    id = Column(String, primary_key=True)  # sound_monitoring, vibration_monitoring, combined
    name = Column(String, nullable=False, unique=True)  # "Sound Monitoring", "Vibration Monitoring"
    description = Column(Text, nullable=True)
    icon = Column(String, nullable=True)  # Icon identifier for UI
    supports_sound = Column(Boolean, default=False)  # Enables SLM features
    supports_vibration = Column(Boolean, default=False)  # Enables seismograph features
    created_at = Column(DateTime, default=datetime.utcnow)


class Project(Base):
    """
    Projects: top-level organization for monitoring work.
    Type-aware to enable/disable features based on project_type_id.

    Project naming convention:
    - project_number: TMI internal ID format xxxx-YY (e.g., "2567-23")
    - client_name: Client/contractor name (e.g., "PJ Dick")
    - name: Project/site name (e.g., "RKM Hall", "CMU Campus")

    Display format: "2567-23 - PJ Dick - RKM Hall"
    Users can search by any of these fields.
    """
    __tablename__ = "projects"

    id = Column(String, primary_key=True, index=True)  # UUID
    project_number = Column(String, nullable=True, index=True)  # TMI ID: xxxx-YY format (e.g., "2567-23")
    name = Column(String, nullable=False, unique=True)  # Project/site name (e.g., "RKM Hall")
    description = Column(Text, nullable=True)
    project_type_id = Column(String, nullable=True)  # Legacy FK to ProjectType.id; use ProjectModule for feature flags
    status = Column(String, default="active")  # active, on_hold, completed, archived, deleted

    # Data collection mode: how field data reaches Terra-View.
    # "remote"  — units have modems; data pulled via FTP/scheduler automatically
    # "manual"  — no modem; SD cards retrieved daily and uploaded by hand
    data_collection_mode = Column(String, default="manual")  # remote | manual

    # Project metadata
    client_name = Column(String, nullable=True, index=True)  # Client name (e.g., "PJ Dick")
    site_address = Column(String, nullable=True)
    site_coordinates = Column(String, nullable=True)  # "lat,lon"
    start_date = Column(Date, nullable=True)
    end_date = Column(Date, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    deleted_at = Column(DateTime, nullable=True)  # Set when status='deleted'; hard delete scheduled after 60 days


class ProjectModule(Base):
    """
    Modules enabled on a project. Each module unlocks a set of features/tabs.
    A project can have zero or more modules (sound_monitoring, vibration_monitoring, etc.).
    """
    __tablename__ = "project_modules"

    id = Column(String, primary_key=True, default=lambda: __import__('uuid').uuid4().__str__())
    project_id = Column(String, nullable=False, index=True)  # FK to projects.id
    module_type = Column(String, nullable=False)  # sound_monitoring | vibration_monitoring | ...
    enabled = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    __table_args__ = (UniqueConstraint("project_id", "module_type", name="uq_project_module"),)


class MonitoringLocation(Base):
    """
    Monitoring locations: generic location for monitoring activities.
    Can be NRL (Noise Recording Location) for sound projects,
    or monitoring point for vibration projects.
    """
    __tablename__ = "monitoring_locations"

    id = Column(String, primary_key=True, index=True)  # UUID
    project_id = Column(String, nullable=False, index=True)  # FK to Project.id
    location_type = Column(String, nullable=False)  # "sound" | "vibration"

    name = Column(String, nullable=False)  # NRL-001, VP-North, etc.
    description = Column(Text, nullable=True)
    coordinates = Column(String, nullable=True)  # "lat,lon"
    address = Column(String, nullable=True)

    # Type-specific metadata stored as JSON
    # For sound: {"ambient_conditions": "urban", "expected_sources": ["traffic"]}
    # For vibration: {"ground_type": "bedrock", "depth": "10m"}
    location_metadata = Column(Text, nullable=True)

    # Soft-removal: NULL means active.  When set, the location is hidden from
    # active surfaces (assign dropdowns, calendar, scheduler, dashboard
    # vibration summary) but historical events generated before this time
    # still attribute to it.  Mirrors the closed-state pattern used by
    # UnitAssignment.assigned_until.
    removed_at     = Column(DateTime, nullable=True)
    removal_reason = Column(Text, nullable=True)

    # Display order within the project's location list.  Operators can
    # drag-and-drop to reorder cards on the project detail page.  Lower
    # values render first; ties fall back to name (alphabetical).  Seeded
    # to alphabetical-index on migration; new locations get max+1.
    sort_order = Column(Integer, default=0, nullable=False)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class UnitAssignment(Base):
    """
    Unit assignments: links devices (SLMs or seismographs) to monitoring locations.
    Supports temporary assignments with assigned_until.
    """
    __tablename__ = "unit_assignments"

    id = Column(String, primary_key=True, index=True)  # UUID
    unit_id = Column(String, nullable=False, index=True)  # FK to RosterUnit.id
    location_id = Column(String, nullable=False, index=True)  # FK to MonitoringLocation.id

    assigned_at = Column(DateTime, default=datetime.utcnow)
    assigned_until = Column(DateTime, nullable=True)  # Null = indefinite
    status = Column(String, default="active")  # active, completed, cancelled
    notes = Column(Text, nullable=True)

    # Denormalized for efficient queries
    device_type = Column(String, nullable=False)  # "slm" | "seismograph"
    project_id = Column(String, nullable=False, index=True)  # FK to Project.id

    # Provenance: how was this assignment created?  Used for auditing,
    # bulk-undo of parser actions, and the Phase 4 deployment timeline.
    #   "manual"                  — operator created via UI
    #   "metadata_backfill"       — auto-created by the metadata parser
    #                                from operator-typed BW event metadata
    #                                (bulk backfill workflow)
    #   "metadata_backfill_swap"  — auto-created by swap-detection
    #                                background job
    source = Column(String, nullable=False, default="manual")

    created_at = Column(DateTime, default=datetime.utcnow)


class MetadataBackfillDecision(Base):
    """
    Per-cluster decisions tracked by the metadata-backfill parser.

    `cluster_id` is the deterministic SHA1 hash of
    (serial, first_event_date, last_event_date), so the same cluster
    produces the same id across re-scans.  The decisions table lets the
    parser remember "I already applied this" or "operator skipped this"
    across scan invocations.
    """
    __tablename__ = "metadata_backfill_decisions"

    cluster_id            = Column(String, primary_key=True)
    status                = Column(String, nullable=False)   # pending | applied | skipped | conflict
    confidence            = Column(String, nullable=False)   # high | medium | low
    decided_at            = Column(DateTime, nullable=True)
    decided_by            = Column(String, nullable=True)    # background | operator | auto-high
    applied_assignment_id = Column(String, nullable=True)    # FK to unit_assignments.id
    notes                 = Column(Text,   nullable=True)
    first_seen_at         = Column(DateTime, nullable=False, default=datetime.utcnow)
    last_seen_at          = Column(DateTime, nullable=False, default=datetime.utcnow)
    serial                = Column(String, nullable=False, index=True)
    project_raw           = Column(String, nullable=True)
    location_raw          = Column(String, nullable=True)
    first_event_ts        = Column(DateTime, nullable=True)
    last_event_ts         = Column(DateTime, nullable=True)
    event_count           = Column(Integer, nullable=False, default=0)


class ScheduledAction(Base):
    """
    Scheduled actions: automation for recording start/stop/download.
    Terra-View executes these by calling SLMM or SFM endpoints.
    """
    __tablename__ = "scheduled_actions"

    id = Column(String, primary_key=True, index=True)  # UUID
    project_id = Column(String, nullable=False, index=True)  # FK to Project.id
    location_id = Column(String, nullable=False, index=True)  # FK to MonitoringLocation.id
    unit_id = Column(String, nullable=True, index=True)  # FK to RosterUnit.id (nullable if location-based)

    action_type = Column(String, nullable=False)  # start, stop, download, cycle, calibrate
    device_type = Column(String, nullable=False)  # "slm" | "seismograph"

    scheduled_time = Column(DateTime, nullable=False, index=True)
    executed_at = Column(DateTime, nullable=True)
    execution_status = Column(String, default="pending")  # pending, completed, failed, cancelled

    # Response from device module (SLMM or SFM)
    module_response = Column(Text, nullable=True)  # JSON
    error_message = Column(Text, nullable=True)

    notes = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)


class MonitoringSession(Base):
    """
    Monitoring sessions: tracks actual monitoring sessions.
    Created when monitoring starts, updated when it stops.
    """
    __tablename__ = "monitoring_sessions"

    id = Column(String, primary_key=True, index=True)  # UUID
    project_id = Column(String, nullable=False, index=True)  # FK to Project.id
    location_id = Column(String, nullable=False, index=True)  # FK to MonitoringLocation.id
    unit_id = Column(String, nullable=True, index=True)  # FK to RosterUnit.id (nullable for offline uploads)

    # Physical device model that produced this session's data (e.g. "NL-43", "NL-53", "NL-32").
    # Null for older records; report code falls back to file-content detection when null.
    device_model = Column(String, nullable=True)

    session_type = Column(String, nullable=False)  # sound | vibration
    started_at = Column(DateTime, nullable=False)
    stopped_at = Column(DateTime, nullable=True)
    duration_seconds = Column(Integer, nullable=True)
    status = Column(String, default="recording")  # recording, completed, failed

    # Human-readable label auto-derived from date/location, editable by user.
    # e.g. "NRL-1 — Sun 2/23 — Night"
    session_label = Column(String, nullable=True)

    # Period classification for report stats columns.
    # weekday_day | weekday_night | weekend_day | weekend_night
    period_type = Column(String, nullable=True)

    # Effective monitoring window (hours 0–23).  Night sessions cross midnight
    # (period_end_hour < period_start_hour).  NULL = no filtering applied.
    # e.g.  Day: start=7, end=19   Night: start=19, end=7
    period_start_hour = Column(Integer, nullable=True)
    period_end_hour   = Column(Integer, nullable=True)

    # For day sessions: the specific calendar date to use for report filtering.
    # Overrides the automatic "last date with daytime rows" heuristic.
    # Null = use heuristic.
    report_date = Column(Date, nullable=True)

    # Snapshot of device configuration at recording time
    session_metadata = Column(Text, nullable=True)  # JSON

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class DataFile(Base):
    """
    Data files: references to recorded data files.
    Terra-View tracks file metadata; actual files stored in data/Projects/ directory.
    """
    __tablename__ = "data_files"

    id = Column(String, primary_key=True, index=True)  # UUID
    session_id = Column(String, nullable=False, index=True)  # FK to MonitoringSession.id

    file_path = Column(String, nullable=False)  # Relative to data/Projects/
    file_type = Column(String, nullable=False)  # wav, csv, mseed, json
    file_size_bytes = Column(Integer, nullable=True)
    downloaded_at = Column(DateTime, nullable=True)
    checksum = Column(String, nullable=True)  # SHA256 or MD5

    # Additional file metadata
    file_metadata = Column(Text, nullable=True)  # JSON

    created_at = Column(DateTime, default=datetime.utcnow)


class ReportTemplate(Base):
    """
    Report templates: saved configurations for generating Excel reports.
    Allows users to save time filter presets, titles, etc. for reuse.
    """
    __tablename__ = "report_templates"

    id = Column(String, primary_key=True, index=True)  # UUID
    name = Column(String, nullable=False)  # "Nighttime Report", "Full Day Report"
    project_id = Column(String, nullable=True)  # Optional: project-specific template

    # Template settings
    report_title = Column(String, default="Background Noise Study")
    start_time = Column(String, nullable=True)  # "19:00" format
    end_time = Column(String, nullable=True)    # "07:00" format
    start_date = Column(String, nullable=True)  # "2025-01-15" format (optional)
    end_date = Column(String, nullable=True)    # "2025-01-20" format (optional)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


# ============================================================================
# Sound Monitoring Scheduler
# ============================================================================

class RecurringSchedule(Base):
    """
    Recurring schedule definitions for automated sound monitoring.

    Supports three schedule types:
    - "weekly_calendar": Select specific days with start/end times (e.g., Mon/Wed/Fri 7pm-7am)
    - "simple_interval": For 24/7 monitoring with daily stop/download/restart cycles
    - "one_off": Single recording session with specific start and end date/time
    """
    __tablename__ = "recurring_schedules"

    id = Column(String, primary_key=True, index=True)  # UUID
    project_id = Column(String, nullable=False, index=True)  # FK to Project.id
    location_id = Column(String, nullable=False, index=True)  # FK to MonitoringLocation.id
    unit_id = Column(String, nullable=True, index=True)  # FK to RosterUnit.id (optional, can use assignment)

    name = Column(String, nullable=False)  # "Weeknight Monitoring", "24/7 Continuous"
    schedule_type = Column(String, nullable=False)  # "weekly_calendar" | "simple_interval" | "one_off"
    device_type = Column(String, nullable=False)  # "slm" | "seismograph"

    # Weekly Calendar fields (schedule_type = "weekly_calendar")
    # JSON format: {
    #   "monday": {"enabled": true, "start": "19:00", "end": "07:00"},
    #   "tuesday": {"enabled": false},
    #   ...
    # }
    weekly_pattern = Column(Text, nullable=True)

    # Simple Interval fields (schedule_type = "simple_interval")
    interval_type = Column(String, nullable=True)  # "daily" | "hourly"
    cycle_time = Column(String, nullable=True)  # "00:00" - time to run stop/download/restart
    include_download = Column(Boolean, default=True)  # Download data before restart

    # One-Off fields (schedule_type = "one_off")
    start_datetime = Column(DateTime, nullable=True)  # Exact start date+time (stored as UTC)
    end_datetime = Column(DateTime, nullable=True)    # Exact end date+time (stored as UTC)

    # Automation options (applies to all schedule types)
    auto_increment_index = Column(Boolean, default=True)  # Auto-increment store/index number before start
    # When True: prevents "overwrite data?" prompts by using a new index each time

    # Shared configuration
    enabled = Column(Boolean, default=True)
    timezone = Column(String, default="America/New_York")

    # Tracking
    last_generated_at = Column(DateTime, nullable=True)  # When actions were last generated
    next_occurrence = Column(DateTime, nullable=True)  # Computed next action time

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class Alert(Base):
    """
    In-app alerts for device status changes and system events.

    Designed for future expansion to email/webhook notifications.
    Currently supports:
    - device_offline: Device became unreachable
    - device_online: Device came back online
    - schedule_failed: Scheduled action failed to execute
    - schedule_completed: Scheduled action completed successfully
    """
    __tablename__ = "alerts"

    id = Column(String, primary_key=True, index=True)  # UUID

    # Alert classification
    alert_type = Column(String, nullable=False)  # "device_offline" | "device_online" | "schedule_failed" | "schedule_completed"
    severity = Column(String, default="warning")  # "info" | "warning" | "critical"

    # Related entities (nullable - may not all apply)
    project_id = Column(String, nullable=True, index=True)
    location_id = Column(String, nullable=True, index=True)
    unit_id = Column(String, nullable=True, index=True)
    schedule_id = Column(String, nullable=True)  # RecurringSchedule or ScheduledAction id

    # Alert content
    title = Column(String, nullable=False)  # "NRL-001 Device Offline"
    message = Column(Text, nullable=True)  # Detailed description
    alert_metadata = Column(Text, nullable=True)  # JSON: additional context data

    # Status tracking
    status = Column(String, default="active")  # "active" | "acknowledged" | "resolved" | "dismissed"
    acknowledged_at = Column(DateTime, nullable=True)
    resolved_at = Column(DateTime, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime, nullable=True)  # Auto-dismiss after this time


# ============================================================================
# Deployment Records
# ============================================================================

class DeploymentRecord(Base):
    """
    Deployment records: tracks each time a unit is sent to the field and returned.

    Each row represents one deployment. The active deployment is the record
    with actual_removal_date IS NULL. The fleet calendar uses this to show
    units as "In Field" and surface their expected return date.

    project_ref is a freeform string for legacy/vibration jobs like "Fay I-80".
    project_id will be populated once those jobs are migrated to proper Project records.
    """
    __tablename__ = "deployment_records"

    id = Column(String, primary_key=True, index=True)  # UUID
    unit_id = Column(String, nullable=False, index=True)  # FK to RosterUnit.id

    deployed_date = Column(Date, nullable=True)               # When unit left the yard
    estimated_removal_date = Column(Date, nullable=True)      # Expected return date
    actual_removal_date = Column(Date, nullable=True)         # Filled in when returned; NULL = still out

    # Project linkage: freeform for legacy jobs, FK for proper project records
    project_ref = Column(String, nullable=True)               # e.g. "Fay I-80" (vibration jobs)
    project_id = Column(String, nullable=True, index=True)    # FK to Project.id (when available)

    location_name = Column(String, nullable=True)             # e.g. "North Gate", "VP-001"
    notes = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


# ============================================================================
# Fleet Calendar & Job Reservations
# ============================================================================

class JobReservation(Base):
    """
    Job reservations: reserve units for future jobs/projects.

    Supports two assignment modes:
    - "specific": Pick exact units (SN-001, SN-002, etc.)
    - "quantity": Reserve a number of units (e.g., "need 8 seismographs")

    Used by the Fleet Calendar to visualize unit availability over time.
    """
    __tablename__ = "job_reservations"

    id = Column(String, primary_key=True, index=True)  # UUID
    name = Column(String, nullable=False)  # "Job A - March deployment"
    project_id = Column(String, nullable=True, index=True)  # Optional FK to Project

    # Date range for the reservation
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=True)  # Nullable = TBD / ongoing
    estimated_end_date = Column(Date, nullable=True)  # For planning when end is TBD
    end_date_tbd = Column(Boolean, default=False)  # True = end date unknown

    # Assignment type: "specific" or "quantity"
    assignment_type = Column(String, nullable=False, default="quantity")

    # For quantity reservations
    device_type = Column(String, default="seismograph")  # seismograph | slm
    quantity_needed = Column(Integer, nullable=True)  # e.g., 8 units
    estimated_units = Column(Integer, nullable=True)

    # Full slot list as JSON: [{"location_name": "North Gate", "unit_id": null}, ...]
    # Includes empty slots (no unit assigned yet). Filled slots are authoritative in JobReservationUnit.
    location_slots = Column(Text, nullable=True)

    # Metadata
    notes = Column(Text, nullable=True)
    color = Column(String, default="#3B82F6")  # For calendar display (blue default)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class JobReservationUnit(Base):
    """
    Links specific units to job reservations.

    Used when:
    - assignment_type="specific": Units are directly assigned
    - assignment_type="quantity": Units can be filled in later

    Supports unit swaps: same reservation can have multiple units with
    different date ranges (e.g., BE17353 Feb-Jun, then BE18438 Jun-Nov).
    """
    __tablename__ = "job_reservation_units"

    id = Column(String, primary_key=True, index=True)  # UUID
    reservation_id = Column(String, nullable=False, index=True)  # FK to JobReservation
    unit_id = Column(String, nullable=False, index=True)  # FK to RosterUnit

    # Unit-specific date range (for swaps) - defaults to reservation dates if null
    unit_start_date = Column(Date, nullable=True)  # When this specific unit starts
    unit_end_date = Column(Date, nullable=True)  # When this unit ends (swap out date)
    unit_end_tbd = Column(Boolean, default=False)  # True = end unknown (until cal expires or job ends)

    # Track how this assignment was made
    assignment_source = Column(String, default="specific")  # "specific" | "filled" | "swap"
    assigned_at = Column(DateTime, default=datetime.utcnow)
    notes = Column(Text, nullable=True)  # "Replacing BE17353" etc.

    # Power requirements for this deployment slot
    power_type = Column(String, nullable=True)  # "ac" | "solar" | None

    # Location identity
    location_name = Column(String, nullable=True)  # e.g. "North Gate", "Main Entrance"
    slot_index = Column(Integer, nullable=True)     # Order within reservation (0-based)


class PendingDeployment(Base):
    """
    Field-captured "I just installed this seismograph" record waiting
    to be classified into a project + location.

    Lifecycle:
      1. Operator captures from the /deploy mobile page — photo (EXIF
         GPS auto-extracted), optional free-text note.  Row created
         with status="awaiting".
      2. Later, at a desk: operator picks a project + location (existing
         or new) and "promotes" the row.  A real UnitAssignment is
         created, this row's status flips to "assigned", and
         resulting_assignment_id points at the new assignment.
      3. Mistakes / abandoned captures → status="cancelled" with a
         cancelled_reason for audit.

    Events emitted by the unit before classification are NOT auto-
    attributed (no UnitAssignment exists yet).  They land in the
    "unattributed" bucket on the unit's events tab.  Once the pending
    deployment is promoted, the new UnitAssignment's window
    retroactively attributes them — same mechanism the metadata-
    backfill tool uses.

    Seismograph-only for v1.  SLM deployments don't follow the same
    "field-install + verify call-home" pattern and are tracked
    elsewhere.
    """
    __tablename__ = "pending_deployments"

    id          = Column(String, primary_key=True, index=True)   # UUID
    unit_id     = Column(String, nullable=False, index=True)     # FK to RosterUnit
    captured_at = Column(DateTime, nullable=False)                # When the photo was taken
    coordinates = Column(String, nullable=True)                   # "lat,lon" from photo EXIF
    operator_note = Column(Text, nullable=True)                   # Free text — site memo

    # Path under data/photos/{unit_id}/.  Just the filename; the unit
    # context lives in unit_id.
    photo_filename = Column(String, nullable=True)

    # Lifecycle.
    #   "awaiting"  — captured, not yet classified
    #   "assigned"  — promoted to a UnitAssignment
    #   "cancelled" — operator marked it as a mistake / abandoned
    status      = Column(String, nullable=False, default="awaiting", index=True)

    promoted_at             = Column(DateTime, nullable=True)
    resulting_assignment_id = Column(String, nullable=True)       # FK to UnitAssignment when promoted

    cancelled_at      = Column(DateTime, nullable=True)
    cancelled_reason  = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)