models.py

from flask_login import UserMixin
from datetime import datetime
from extensions import db

class Organization(db.Model):
    __tablename__ = 'organizations'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False, unique=True)
    sector_type = db.Column(db.String(100), nullable=False)
    registration_date = db.Column(db.Date, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    users = db.relationship('User', backref='organization', lazy=True)
    kri_scores = db.relationship('KRIScore', backref='organization', lazy=True)

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), nullable=False, unique=True)
    password_hash = db.Column(db.String(200), nullable=False)
    role = db.Column(db.String(20), nullable=False, default='org_user')
    organization_id = db.Column(db.Integer, db.ForeignKey('organizations.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class KRIDefinition(db.Model):
    __tablename__ = 'kri_definitions'
    id = db.Column(db.Integer, primary_key=True)
    kri_name = db.Column(db.String(200), nullable=False)
    weight_pct = db.Column(db.Float, nullable=False)
    regulatory_reference = db.Column(db.String(300))
    measurement_method = db.Column(db.String(500))
    thresholds = db.Column(db.JSON, nullable=False)

class KRIScore(db.Model):
    __tablename__ = 'kri_scores'
    id = db.Column(db.Integer, primary_key=True)
    organization_id = db.Column(db.Integer, db.ForeignKey('organizations.id'), nullable=False)
    year = db.Column(db.Integer, nullable=False)
    kri_id = db.Column(db.Integer, db.ForeignKey('kri_definitions.id'), nullable=False)
    raw_value = db.Column(db.Float, nullable=False)
    numerical_score = db.Column(db.Integer, nullable=False)
    contribution = db.Column(db.Float, nullable=False)
    calculated_at = db.Column(db.DateTime, default=datetime.utcnow)
    __table_args__ = (db.UniqueConstraint('organization_id', 'year', 'kri_id', name='unique_org_year_kri'),)

class AuditLog(db.Model):
    __tablename__ = 'audit_logs'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    action = db.Column(db.String(200), nullable=False)
    details = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    user = db.relationship('User', backref='audit_logs')