from datetime import datetime
from app.extensions import db


class NilaiImportBatch(db.Model):
    __tablename__ = 'nilai_import_batches'

    id = db.Column(db.Integer, primary_key=True)
    school_id = db.Column(db.Integer, db.ForeignKey('schools.id'), nullable=False)
    guru_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    default_kelas_id = db.Column(db.Integer, db.ForeignKey('kelas.id'), nullable=True)
    default_mapel_id = db.Column(db.Integer, db.ForeignKey('mata_pelajaran.id'), nullable=True)
    default_type = db.Column(db.String(20), nullable=False, default='tugas')
    total_rows = db.Column(db.Integer, default=0, nullable=False)
    created_count = db.Column(db.Integer, default=0, nullable=False)
    updated_count = db.Column(db.Integer, default=0, nullable=False)
    skipped_count = db.Column(db.Integer, default=0, nullable=False)
    is_rolled_back = db.Column(db.Boolean, default=False, nullable=False)
    rolled_back_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    guru = db.relationship('User', foreign_keys=[guru_id], backref='nilai_import_batches')
    kelas = db.relationship('Kelas', foreign_keys=[default_kelas_id])
    mata_pelajaran = db.relationship('MataPelajaran', foreign_keys=[default_mapel_id])
    rows = db.relationship(
        'NilaiImportRow',
        backref='batch',
        lazy='dynamic',
        cascade='all, delete-orphan',
        order_by='NilaiImportRow.row_number',
    )


class NilaiImportRow(db.Model):
    __tablename__ = 'nilai_import_rows'

    id = db.Column(db.Integer, primary_key=True)
    batch_id = db.Column(db.Integer, db.ForeignKey('nilai_import_batches.id'), nullable=False)
    school_id = db.Column(db.Integer, db.ForeignKey('schools.id'), nullable=False)
    nilai_id = db.Column(db.Integer, db.ForeignKey('nilai.id'), nullable=True)
    row_number = db.Column(db.Integer, nullable=False)
    action = db.Column(db.String(20), nullable=False)
    status = db.Column(db.String(20), nullable=False)
    message = db.Column(db.Text, nullable=True)
    murid_name = db.Column(db.String(150), nullable=True)
    kelas_name = db.Column(db.String(100), nullable=True)
    mapel_name = db.Column(db.String(100), nullable=True)
    surah_name = db.Column(db.String(200), nullable=True)
    ayat = db.Column(db.String(50), nullable=True)
    nilai_type = db.Column(db.String(20), nullable=True)
    score = db.Column(db.Numeric(5, 2), nullable=True)
    description = db.Column(db.String(255), nullable=True)
    raw_data = db.Column(db.JSON, nullable=True)
    previous_score = db.Column(db.Numeric(5, 2), nullable=True)
    previous_description = db.Column(db.String(255), nullable=True)
    previous_type = db.Column(db.String(20), nullable=True)
    rollback_applied = db.Column(db.Boolean, default=False, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    nilai = db.relationship('Nilai', backref='import_rows')