from app import db, login_manager, bcrypt
from flask_login import UserMixin
from datetime import datetime
from decimal import Decimal

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    full_name = db.Column(db.String(150), nullable=False)
    role = db.Column(db.String(50), default='staff')  # admin, manager, staff, finance
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def set_password(self, password):
        self.password_hash = bcrypt.generate_password_hash(password).decode('utf-8')

    def check_password(self, password):
        return bcrypt.check_password_hash(self.password_hash, password)

    def __repr__(self):
        return f'<User {self.username}>'


class Project(db.Model):
    __tablename__ = 'projects'
    id = db.Column(db.Integer, primary_key=True)
    project_code = db.Column(db.String(50), unique=True, nullable=False)
    project_name = db.Column(db.String(255), nullable=False)
    client_name = db.Column(db.String(255), nullable=False)  # Instansi pemerintah
    contract_number = db.Column(db.String(100))
    contract_date = db.Column(db.Date)
    start_date = db.Column(db.Date, nullable=False)
    end_date = db.Column(db.Date, nullable=False)
    contract_value = db.Column(db.Numeric(18, 2), nullable=False, default=0)
    status = db.Column(db.String(50), default='active')  # active, completed, suspended, cancelled
    description = db.Column(db.Text)
    performance_obligation = db.Column(db.Text)  # PSAK 72: Kewajiban Pelaksanaan
    satisfaction_method = db.Column(db.String(100), default='overtime')  # overtime / point_in_time
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    budgets = db.relationship('ProjectBudget', backref='project', lazy='dynamic', cascade='all, delete-orphan')
    cost_entries = db.relationship('ProjectCost', backref='project', lazy='dynamic', cascade='all, delete-orphan')
    purchase_requests = db.relationship('PurchaseRequest', backref=db.backref('project', lazy='select'), lazy='dynamic')
    purchase_orders = db.relationship('PurchaseOrder', lazy='dynamic')
    revenues = db.relationship('RevenueRecognition', backref='project', lazy='dynamic', cascade='all, delete-orphan')

    @property
    def total_budget(self):
        result = db.session.query(db.func.sum(ProjectBudget.amount)).filter_by(project_id=self.id).scalar()
        return result or Decimal('0')

    @property
    def total_cost(self):
        result = db.session.query(db.func.sum(ProjectCost.amount)).filter_by(project_id=self.id).scalar()
        return result or Decimal('0')

    @property
    def completion_percentage(self):
        if self.total_budget == 0:
            return 0
        pct = float(self.total_cost / self.total_budget * 100)
        return min(round(pct, 2), 100)

    @property
    def recognized_revenue(self):
        result = db.session.query(db.func.sum(RevenueRecognition.recognized_amount)).filter_by(project_id=self.id).scalar()
        return result or Decimal('0')

    def __repr__(self):
        return f'<Project {self.project_code}>'


class ProjectBudget(db.Model):
    __tablename__ = 'project_budgets'
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    cost_category = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(255))
    amount = db.Column(db.Numeric(18, 2), nullable=False, default=0)
    unit = db.Column(db.String(50))
    quantity = db.Column(db.Numeric(10, 2), default=1)
    unit_price = db.Column(db.Numeric(18, 2), default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))

    @property
    def actual_cost(self):
        result = db.session.query(db.func.sum(ProjectCost.amount)).filter_by(
            project_id=self.project_id, cost_category=self.cost_category
        ).scalar()
        return result or Decimal('0')

    @property
    def variance(self):
        return self.amount - self.actual_cost


class ProjectCost(db.Model):
    __tablename__ = 'project_costs'
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    cost_category = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(255), nullable=False)
    amount = db.Column(db.Numeric(18, 2), nullable=False)
    transaction_date = db.Column(db.Date, nullable=False)
    reference_number = db.Column(db.String(100))
    vendor_name = db.Column(db.String(200))
    purchase_order_id = db.Column(db.Integer, db.ForeignKey('purchase_orders.id'), nullable=True)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


class RevenueRecognition(db.Model):
    """PSAK 72 - Pengakuan Pendapatan"""
    __tablename__ = 'revenue_recognitions'
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    recognition_date = db.Column(db.Date, nullable=False)
    period = db.Column(db.String(20))  # YYYY-MM
    completion_percentage = db.Column(db.Numeric(5, 2))
    contract_value = db.Column(db.Numeric(18, 2))
    recognized_amount = db.Column(db.Numeric(18, 2), nullable=False)
    cumulative_recognized = db.Column(db.Numeric(18, 2))
    notes = db.Column(db.Text)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


class PurchaseRequest(db.Model):
    __tablename__ = 'purchase_requests'
    id = db.Column(db.Integer, primary_key=True)
    pr_number = db.Column(db.String(50), unique=True, nullable=False)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    request_date = db.Column(db.Date, nullable=False)
    needed_date = db.Column(db.Date)
    status = db.Column(db.String(50), default='draft')  # draft, submitted, approved, rejected, ordered
    priority = db.Column(db.String(20), default='normal')  # low, normal, high, urgent
    notes = db.Column(db.Text)
    total_amount = db.Column(db.Numeric(18, 2), default=0)
    requested_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    approved_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    approved_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    items = db.relationship('PurchaseRequestItem', backref='purchase_request', lazy='dynamic', cascade='all, delete-orphan')
    purchase_orders = db.relationship('PurchaseOrder', backref=db.backref('purchase_request', lazy='select'), lazy='dynamic')


class PurchaseRequestItem(db.Model):
    __tablename__ = 'purchase_request_items'
    id = db.Column(db.Integer, primary_key=True)
    pr_id = db.Column(db.Integer, db.ForeignKey('purchase_requests.id'), nullable=False)
    item_name = db.Column(db.String(255), nullable=False)
    specification = db.Column(db.Text)
    quantity = db.Column(db.Numeric(10, 2), nullable=False)
    unit = db.Column(db.String(50), nullable=False)
    estimated_price = db.Column(db.Numeric(18, 2), default=0)
    cost_category = db.Column(db.String(100))
    notes = db.Column(db.Text)

    @property
    def total_price(self):
        return self.quantity * self.estimated_price


class PurchaseOrder(db.Model):
    __tablename__ = 'purchase_orders'
    id = db.Column(db.Integer, primary_key=True)
    po_number = db.Column(db.String(50), unique=True, nullable=False)
    pr_id = db.Column(db.Integer, db.ForeignKey('purchase_requests.id'), nullable=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    vendor_id = db.Column(db.Integer, db.ForeignKey('vendors.id'), nullable=False)
    order_date = db.Column(db.Date, nullable=False)
    delivery_date = db.Column(db.Date)
    status = db.Column(db.String(50), default='draft')  # draft, sent, partial, received, cancelled
    payment_terms = db.Column(db.String(100))
    subtotal = db.Column(db.Numeric(18, 2), default=0)
    tax_amount = db.Column(db.Numeric(18, 2), default=0)
    total_amount = db.Column(db.Numeric(18, 2), default=0)
    notes = db.Column(db.Text)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    items = db.relationship('PurchaseOrderItem', backref='purchase_order', lazy='dynamic', cascade='all, delete-orphan')
    costs = db.relationship('ProjectCost', backref='purchase_order', lazy='dynamic')
    project = db.relationship('Project', foreign_keys=[project_id], overlaps="purchase_orders")


class PurchaseOrderItem(db.Model):
    __tablename__ = 'purchase_order_items'
    id = db.Column(db.Integer, primary_key=True)
    po_id = db.Column(db.Integer, db.ForeignKey('purchase_orders.id'), nullable=False)
    item_name = db.Column(db.String(255), nullable=False)
    specification = db.Column(db.Text)
    quantity = db.Column(db.Numeric(10, 2), nullable=False)
    unit = db.Column(db.String(50), nullable=False)
    unit_price = db.Column(db.Numeric(18, 2), nullable=False)
    tax_rate = db.Column(db.Numeric(5, 2), default=11)  # PPN 11%
    total_price = db.Column(db.Numeric(18, 2), default=0)
    received_qty = db.Column(db.Numeric(10, 2), default=0)


class Vendor(db.Model):
    __tablename__ = 'vendors'
    id = db.Column(db.Integer, primary_key=True)
    vendor_code = db.Column(db.String(50), unique=True, nullable=False)
    vendor_name = db.Column(db.String(255), nullable=False)
    npwp = db.Column(db.String(30))
    address = db.Column(db.Text)
    phone = db.Column(db.String(30))
    email = db.Column(db.String(120))
    contact_person = db.Column(db.String(150))
    bank_name = db.Column(db.String(100))
    bank_account = db.Column(db.String(50))
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    purchase_orders = db.relationship('PurchaseOrder', backref=db.backref('vendor', lazy='select'), lazy='dynamic')


class Document(db.Model):
    __tablename__ = 'documents'
    id = db.Column(db.Integer, primary_key=True)
    doc_number = db.Column(db.String(100))
    doc_name = db.Column(db.String(255), nullable=False)
    doc_type = db.Column(db.String(100), nullable=False)  # kontrak, spm, bast, invoice, dll
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=True)
    related_type = db.Column(db.String(50))   # project, pr, po
    related_id = db.Column(db.Integer)
    file_path = db.Column(db.String(500), nullable=False)
    file_name = db.Column(db.String(255), nullable=False)
    file_size = db.Column(db.Integer)
    file_type = db.Column(db.String(50))
    description = db.Column(db.Text)
    tags = db.Column(db.String(500))
    uploaded_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    project = db.relationship('Project', foreign_keys=[project_id])
