"""Manual labour records and scheduling resources, never login accounts."""
from datetime import date, datetime
import json
from zoneinfo import ZoneInfo
from fastapi import HTTPException, Request
from pydantic import Field, StrictInt, field_validator
from .models import Strict
from .material_rules import Identity, VersionNumber, Version, price
from .store import one, many, uid, now, audit

LONDON = ZoneInfo('Europe/London')
SCHEMA = '''
CREATE TABLE IF NOT EXISTS team_resources(
 id TEXT PRIMARY KEY,name TEXT NOT NULL,role TEXT NOT NULL,hourly_cost_pence INTEGER NOT NULL CHECK(hourly_cost_pence>=0),
 colour TEXT NOT NULL,version INTEGER NOT NULL DEFAULT 1,archived INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS time_entries(
 id TEXT PRIMARY KEY,job_id TEXT NOT NULL REFERENCES jobs(id),resource_id TEXT NOT NULL REFERENCES team_resources(id),
 resource_snapshot TEXT NOT NULL,work_date TEXT NOT NULL,duration_minutes INTEGER NOT NULL CHECK(duration_minutes>0 AND duration_minutes<=1440),
 hourly_cost_pence INTEGER NOT NULL CHECK(hourly_cost_pence>=0),cost_pence INTEGER NOT NULL CHECK(cost_pence>=0),note TEXT NOT NULL,
 version INTEGER NOT NULL DEFAULT 1,archived INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,updated_at TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS time_job ON time_entries(job_id);
CREATE INDEX IF NOT EXISTS time_person_date ON time_entries(resource_id,work_date);
CREATE TABLE IF NOT EXISTS workforce_audit(id INTEGER PRIMARY KEY AUTOINCREMENT,kind TEXT NOT NULL,record_id TEXT NOT NULL,
 actor TEXT NOT NULL,action TEXT NOT NULL,before_value TEXT,after_value TEXT,created_at TEXT NOT NULL);
'''


def today_london():
    return datetime.now(LONDON).date()


def work_date(value):
    # Source time_entries.py validates dates too; this variant uses London and
    # rejects future recorded work instead of allowing an extra UTC day.
    parsed = date.fromisoformat(value)
    if len(value) != 10 or not date(2020,1,1) <= parsed <= today_london():
        raise ValueError('Use a work date from 2020 through today in Europe/London.')
    return parsed.isoformat()


def labour_cost(minutes, hourly_pence):
    # Round once per entry, half-up to exact pence, without binary floats.
    cost = (minutes * hourly_pence + 30) // 60
    if cost > 100_000_000:
        raise HTTPException(422,'One labour entry cannot exceed £1,000,000.')
    return cost


class ResourceFields(Strict):
    name: str = Field(min_length=2,max_length=100)
    role: str = Field(default='',max_length=100)
    hourly_cost: str = Field(max_length=30)
    colour: str = Field(default='#245648',pattern=r'^#[0-9a-fA-F]{6}$')

    @field_validator('hourly_cost')
    @classmethod
    def valid_price(cls,value):
        price(value,'Hourly cost');return value


class ResourceEdit(ResourceFields):
    version: VersionNumber


class TimeFields(Strict):
    work_date: str
    duration_minutes: StrictInt = Field(ge=1,le=1440)
    note: str = Field(default='',max_length=500)

    @field_validator('work_date')
    @classmethod
    def valid_date(cls,value):
        return work_date(value)


class TimeCreate(TimeFields):
    job_id: Identity
    resource_id: Identity
    resource_version: VersionNumber
    hourly_cost_override: str = Field(default='',max_length=30)

    @field_validator('hourly_cost_override')
    @classmethod
    def valid_price(cls,value):
        if value: price(value,'Hourly cost')
        return value


class TimeEdit(TimeFields):
    version: VersionNumber
    hourly_cost: str = Field(max_length=30)

    @field_validator('hourly_cost')
    @classmethod
    def valid_price(cls,value):
        price(value,'Hourly cost');return value


def required(row):
    if not row: raise HTTPException(404,'Record not found in this business.')
    return row


def current(row,version,active=True):
    required(row)
    if row['version']!=version: raise HTTPException(409,'This record changed. Refresh before trying again.')
    if active and row['archived']: raise HTTPException(409,'Restore this archived record before using it.')
    return row


def log(db,s,kind,action,before,after):
    row=after or before
    db.execute('INSERT INTO workforce_audit(kind,record_id,actor,action,before_value,after_value,created_at) VALUES(?,?,?,?,?,?,?)',(kind,row['id'],s['subject'],action,json.dumps(before) if before else None,json.dumps(after) if after else None,now()))
    audit(db,'workforce.'+kind+'.'+action,row['id'])


def day_capacity(db,rid,day,minutes,exclude=''):
    total=db.execute('SELECT COALESCE(SUM(duration_minutes),0) FROM time_entries WHERE resource_id=? AND work_date=? AND archived=0 AND id!=?',(rid,day,exclude)).fetchone()[0]
    if total+minutes>1440: raise HTTPException(409,'This person would have more than 24 hours recorded on that date across all jobs.')


def decode_time(row):
    if row: row['resource_snapshot']=json.loads(row['resource_snapshot'])
    return row


def mount_workforce(app,store,session,once):
    @app.get('/api/team')
    def team(request:Request,archived:bool=False):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            return many(db,'SELECT * FROM team_resources WHERE archived=? ORDER BY name COLLATE NOCASE',(int(archived),))

    @app.post('/api/team')
    def create_resource(payload:ResourceFields,request:Request):
        s=session(request,csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                rid,stamp=uid(),now()
                db.execute('INSERT INTO team_resources(id,name,role,hourly_cost_pence,colour,created_at,updated_at) VALUES(?,?,?,?,?,?,?)',(rid,payload.name,payload.role,price(payload.hourly_cost),payload.colour,stamp,stamp))
                row=one(db,'SELECT * FROM team_resources WHERE id=?',(rid,));log(db,s,'resource','created',None,row);return row
            return once(db,request,payload.model_dump(),action)

    @app.put('/api/team/{resource_id}')
    def edit_resource(resource_id:str,payload:ResourceEdit,request:Request):
        s=session(request,csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                row=current(one(db,'SELECT * FROM team_resources WHERE id=?',(resource_id,)),payload.version)
                db.execute('UPDATE team_resources SET name=?,role=?,hourly_cost_pence=?,colour=?,version=version+1,updated_at=? WHERE id=?',(payload.name,payload.role,price(payload.hourly_cost),payload.colour,now(),resource_id))
                after=one(db,'SELECT * FROM team_resources WHERE id=?',(resource_id,));log(db,s,'resource','edited',row,after);return after
            return once(db,request,payload.model_dump(),action)

    @app.post('/api/team/{resource_id}/{action}')
    def archive_resource(resource_id:str,action:str,payload:Version,request:Request):
        s=session(request,csrf=True)
        if action not in ('archive','restore'):raise HTTPException(404,'Route not found.')
        with store.tenant(s['business_id']) as db:
            def change():
                row=current(one(db,'SELECT * FROM team_resources WHERE id=?',(resource_id,)),payload.version,False)
                target=int(action=='archive')
                if row['archived']==target:raise HTTPException(409,'This person is already in that state.')
                if target and one(db,"SELECT name FROM sqlite_master WHERE type='table' AND name='calendar_bookings'"):
                    bookings=many(db,'SELECT resource_ids FROM calendar_bookings WHERE archived=0 AND end_utc>?',(now(),))
                    if any(resource_id in json.loads(b['resource_ids']) for b in bookings):raise HTTPException(409,'Reassign or archive this person’s future bookings before archiving them.')
                db.execute('UPDATE team_resources SET archived=?,version=version+1,updated_at=? WHERE id=?',(target,now(),resource_id))
                after=one(db,'SELECT * FROM team_resources WHERE id=?',(resource_id,));log(db,s,'resource',action,row,after);return after
            return once(db,request,payload.model_dump(),change)

    @app.get('/api/jobs/{job_id}/time')
    def time_list(job_id:str,request:Request,archived:bool=False):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            required(one(db,'SELECT id FROM jobs WHERE id=?',(job_id,)))
            return [decode_time(r) for r in many(db,"SELECT t.*,CASE WHEN l.time_id IS NULL THEN 'manual' ELSE 'field' END AS source_kind FROM time_entries t LEFT JOIN field_time_links l ON l.time_id=t.id WHERE t.job_id=? AND t.archived=? ORDER BY t.work_date DESC,t.created_at DESC,t.rowid DESC",(job_id,int(archived)))]

    @app.post('/api/time')
    def create_time(payload:TimeCreate,request:Request):
        s=session(request,csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                required(one(db,'SELECT id FROM jobs WHERE id=?',(payload.job_id,)))
                person=current(one(db,'SELECT * FROM team_resources WHERE id=?',(payload.resource_id,)),payload.resource_version)
                day_capacity(db,person['id'],payload.work_date,payload.duration_minutes)
                rate=price(payload.hourly_cost_override) if payload.hourly_cost_override else person['hourly_cost_pence']
                tid,stamp=uid(),now()
                snapshot={k:person[k] for k in ('id','name','role','hourly_cost_pence','version')}
                db.execute('INSERT INTO time_entries(id,job_id,resource_id,resource_snapshot,work_date,duration_minutes,hourly_cost_pence,cost_pence,note,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)',(tid,payload.job_id,person['id'],json.dumps(snapshot),payload.work_date,payload.duration_minutes,rate,labour_cost(payload.duration_minutes,rate),payload.note,stamp,stamp))
                row=decode_time(one(db,'SELECT * FROM time_entries WHERE id=?',(tid,)));log(db,s,'time','created',None,row);return row
            return once(db,request,payload.model_dump(),action)

    @app.put('/api/time/{entry_id}')
    def edit_time(entry_id:str,payload:TimeEdit,request:Request):
        s=session(request,csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                if one(db,'SELECT time_id FROM field_time_links WHERE time_id=?',(entry_id,)):raise HTTPException(409,'Correct generated labour through its Field work review, so durations and source claims stay consistent.')
                row=current(one(db,'SELECT * FROM time_entries WHERE id=?',(entry_id,)),payload.version)
                day_capacity(db,row['resource_id'],payload.work_date,payload.duration_minutes,entry_id)
                rate=price(payload.hourly_cost)
                db.execute('UPDATE time_entries SET work_date=?,duration_minutes=?,hourly_cost_pence=?,cost_pence=?,note=?,version=version+1,updated_at=? WHERE id=?',(payload.work_date,payload.duration_minutes,rate,labour_cost(payload.duration_minutes,rate),payload.note,now(),entry_id))
                after=decode_time(one(db,'SELECT * FROM time_entries WHERE id=?',(entry_id,)));log(db,s,'time','corrected',decode_time(row),after);return after
            return once(db,request,payload.model_dump(),action)

    @app.get('/api/time/{entry_id}/history')
    def time_history(entry_id:str,request:Request):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            required(one(db,'SELECT id FROM time_entries WHERE id=?',(entry_id,)))
            rows=many(db,"SELECT action,before_value,after_value,created_at FROM workforce_audit WHERE kind='time' AND record_id=? ORDER BY id DESC",(entry_id,))
            for row in rows:
                for k in ('before_value','after_value'):row[k]=json.loads(row[k]) if row[k] else None
            return rows

    @app.post('/api/time/{entry_id}/{action}')
    def archive_time(entry_id:str,action:str,payload:Version,request:Request):
        s=session(request,csrf=True)
        if action not in ('archive','restore'):raise HTTPException(404,'Route not found.')
        with store.tenant(s['business_id']) as db:
            def change():
                if one(db,'SELECT time_id FROM field_time_links WHERE time_id=?',(entry_id,)):raise HTTPException(409,'Archive or correct generated labour through its Field work review.')
                row=current(one(db,'SELECT * FROM time_entries WHERE id=?',(entry_id,)),payload.version,False)
                target=int(action=='archive')
                if row['archived']==target:raise HTTPException(409,'This time entry is already in that state.')
                if not target:day_capacity(db,row['resource_id'],row['work_date'],row['duration_minutes'],entry_id)
                db.execute('UPDATE time_entries SET archived=?,version=version+1,updated_at=? WHERE id=?',(target,now(),entry_id))
                after=decode_time(one(db,'SELECT * FROM time_entries WHERE id=?',(entry_id,)));log(db,s,'time',action,decode_time(row),after);return after
            return once(db,request,payload.model_dump(),change)

    @app.get('/api/jobs/{job_id}/costs')
    def costs(job_id:str,request:Request):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            required(one(db,'SELECT id FROM jobs WHERE id=?',(job_id,)))
            labour=one(db,'SELECT COALESCE(SUM(duration_minutes),0) AS minutes,COALESCE(SUM(cost_pence),0) AS cost_pence FROM time_entries WHERE job_id=? AND archived=0',(job_id,))
            material={r['status']:r['cost_pence'] for r in many(db,'SELECT status,SUM(total_pence) AS cost_pence FROM job_materials WHERE job_id=? AND archived=0 GROUP BY status',(job_id,))}
            all_material=sum(material.values());received=material.get('received',0)
            invoices=db.execute("SELECT COALESCE(SUM(total_pence),0) FROM invoices WHERE job_id=? AND status='issued'",(job_id,)).fetchone()[0]
            payments=db.execute('SELECT COALESCE(SUM(p.amount_pence),0) FROM payments p JOIN invoices i ON i.id=p.invoice_id WHERE i.job_id=? AND p.id NOT IN (SELECT payment_id FROM payment_reversals)',(job_id,)).fetchone()[0]
            return {'labour':labour,'materials':{k:material.get(k,0) for k in ('to_order','ordered','received')},'all_materials_pence':all_material,'received_materials_pence':received,
                    'recorded_work_cost_pence':labour['cost_pence']+received,'including_planned_materials_pence':labour['cost_pence']+all_material,
                    'invoiced_value_pence':invoices,'recorded_payments_pence':payments,'currency':'GBP','timezone':'Europe/London',
                    'basis':'Labour is an estimate at each saved internal cost rate. Material costs are entered amounts. VAT, overheads, tax and profit are not calculated.'}