from datetime import datetime
from app.extensions import db


class Dataset(db.Model):
    __tablename__ = 'datasets'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    original_filename = db.Column(db.String(255))

    # Summary stats computed on upload
    num_animals = db.Column(db.Integer)
    num_fixes = db.Column(db.Integer)
    time_start = db.Column(db.DateTime)
    time_end = db.Column(db.DateTime)
    bbox_min_lat = db.Column(db.Float)
    bbox_min_lon = db.Column(db.Float)
    bbox_max_lat = db.Column(db.Float)
    bbox_max_lon = db.Column(db.Float)

    # Column mapping from original CSV
    column_mapping = db.Column(db.JSON)
    upload_status = db.Column(db.String(20), default='processing')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    data_points = db.relationship('DataPoint', backref='dataset',
                                  lazy='dynamic', cascade='all, delete-orphan')
    analysis_runs = db.relationship('AnalysisRun', backref='dataset',
                                    lazy='dynamic', cascade='all, delete-orphan')


class DataPoint(db.Model):
    __tablename__ = 'data_points'

    id = db.Column(db.BigInteger, primary_key=True)
    dataset_id = db.Column(db.Integer, db.ForeignKey('datasets.id'), nullable=False)
    animal_id = db.Column(db.String(255), nullable=False)
    timestamp = db.Column(db.DateTime, nullable=False)
    lat = db.Column(db.Float, nullable=False)
    lon = db.Column(db.Float, nullable=False)

    # Optional sensor data as JSON
    sensor_data = db.Column(db.JSON)

    # Derived fields computed on ingest
    speed = db.Column(db.Float)          # m/s
    step_length = db.Column(db.Float)    # meters
    turning_angle = db.Column(db.Float)  # radians

    __table_args__ = (
        db.Index('idx_dp_dataset', 'dataset_id'),
        db.Index('idx_dp_animal', 'dataset_id', 'animal_id'),
        db.Index('idx_dp_time', 'dataset_id', 'timestamp'),
    )