"""Private field records, server-clock capture and reviewed document sources."""
from typing import Literal
from datetime import datetime,timezone
from pydantic import Field,model_validator,field_validator
from .models import Strict,Version,Line
from .material_rules import Identity,quantity,price
from .workforce import work_date

SCHEMA='''
CREATE TABLE IF NOT EXISTS field_logs(id TEXT PRIMARY KEY,job_id TEXT NOT NULL REFERENCES jobs(id),customer_id TEXT NOT NULL REFERENCES customers(id),author_id TEXT NOT NULL,membership_id TEXT NOT NULL,kind TEXT NOT NULL,content TEXT NOT NULL,work_date TEXT NOT NULL,quantity TEXT NOT NULL,unit TEXT NOT NULL,photo_ids TEXT NOT NULL,reviewed INTEGER NOT NULL DEFAULT 0,archived INTEGER NOT NULL DEFAULT 0,version INTEGER NOT NULL DEFAULT 1,created_at TEXT NOT NULL,updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS field_log_history(id INTEGER PRIMARY KEY AUTOINCREMENT,log_id TEXT NOT NULL REFERENCES field_logs(id),action TEXT NOT NULL,actor_id TEXT NOT NULL,membership_id TEXT NOT NULL,snapshot TEXT NOT NULL,reason TEXT NOT NULL,created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS field_sessions(id TEXT PRIMARY KEY,job_id TEXT NOT NULL REFERENCES jobs(id),customer_id TEXT NOT NULL REFERENCES customers(id),author_id TEXT NOT NULL,membership_id TEXT NOT NULL,resources TEXT NOT NULL,state TEXT NOT NULL,reviewed_intervals TEXT,review_reason TEXT NOT NULL DEFAULT '',note TEXT NOT NULL DEFAULT '',generation INTEGER NOT NULL DEFAULT 0,version INTEGER NOT NULL DEFAULT 1,created_at TEXT NOT NULL,updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS field_intervals(id TEXT PRIMARY KEY,session_id TEXT NOT NULL REFERENCES field_sessions(id),started_at TEXT NOT NULL,last_seen_at TEXT NOT NULL,ended_at TEXT,boot_id TEXT NOT NULL,last_monotonic REAL NOT NULL,end_reason TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS field_one_open_interval ON field_intervals(session_id) WHERE ended_at IS NULL;
CREATE TABLE IF NOT EXISTS field_resource_locks(resource_id TEXT PRIMARY KEY REFERENCES team_resources(id),session_id TEXT NOT NULL REFERENCES field_sessions(id));
CREATE TABLE IF NOT EXISTS field_history(id INTEGER PRIMARY KEY AUTOINCREMENT,session_id TEXT NOT NULL REFERENCES field_sessions(id),action TEXT NOT NULL,actor_id TEXT,membership_id TEXT,snapshot TEXT NOT NULL,detail TEXT NOT NULL,created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS field_time_links(time_id TEXT PRIMARY KEY REFERENCES time_entries(id),session_id TEXT NOT NULL REFERENCES field_sessions(id),generation INTEGER NOT NULL,resource_id TEXT NOT NULL REFERENCES team_resources(id),work_date TEXT NOT NULL,active INTEGER NOT NULL DEFAULT 1,UNIQUE(session_id,generation,resource_id,work_date));
CREATE TABLE IF NOT EXISTS field_material_links(log_id TEXT PRIMARY KEY REFERENCES field_logs(id),material_id TEXT UNIQUE NOT NULL REFERENCES job_materials(id));
CREATE TABLE IF NOT EXISTS document_sources(id TEXT PRIMARY KEY,kind TEXT NOT NULL,document_id TEXT NOT NULL,job_id TEXT NOT NULL REFERENCES jobs(id),customer_id TEXT NOT NULL REFERENCES customers(id),source_kind TEXT NOT NULL,source_id TEXT NOT NULL,source_version INTEGER NOT NULL,source_snapshot TEXT NOT NULL,line_index INTEGER NOT NULL,line_snapshot TEXT NOT NULL,state TEXT NOT NULL,created_at TEXT NOT NULL,updated_at TEXT NOT NULL);
CREATE UNIQUE INDEX IF NOT EXISTS source_active_claim ON document_sources(source_kind,source_id) WHERE state IN ('reserved','issued');
CREATE TABLE IF NOT EXISTS source_history(id INTEGER PRIMARY KEY AUTOINCREMENT,claim_id TEXT NOT NULL REFERENCES document_sources(id),action TEXT NOT NULL,actor_id TEXT NOT NULL,snapshot TEXT NOT NULL,created_at TEXT NOT NULL);
'''

class LogFields(Strict):
    kind:Literal['note','materials']='note'
    content:str=Field(min_length=1,max_length=3000)
    work_date:str
    quantity:str=Field(default='1',max_length=20)
    unit:str=Field(default='each',min_length=1,max_length=30)
    photo_ids:list[Identity]=Field(default_factory=list,max_length=20)
    @field_validator('work_date')
    @classmethod
    def day(cls,value):return work_date(value)
    @field_validator('quantity')
    @classmethod
    def qty(cls,value):return quantity(value)
    @field_validator('photo_ids')
    @classmethod
    def photos(cls,value):
        if len(value)!=len(set(value)):raise ValueError('Select each photo once.')
        return value
class LogEdit(LogFields,Version):
    reason:str=Field(default='',max_length=500)
class Review(Version):
    reason:str=Field(min_length=3,max_length=500)
class MaterialConversion(Review):
    cost_price:str=Field(max_length=30)
    status:Literal['to_order','ordered','received']='received'
    @field_validator('cost_price')
    @classmethod
    def cost(cls,value):price(value);return value
class TimerStart(Strict):
    resource_ids:list[Identity]=Field(min_length=1,max_length=2)
    note:str=Field(default='',max_length=500)
    @field_validator('resource_ids')
    @classmethod
    def resources(cls,value):
        if len(set(value))!=len(value):raise ValueError('Select distinct team members.')
        return value
class Interval(Strict):
    start:str
    end:str
    @field_validator('start','end')
    @classmethod
    def instant(cls,value):
        d=datetime.fromisoformat(value.replace('Z','+00:00'))
        if d.tzinfo is None or d.year<2020:raise ValueError('Use a timestamp with its UTC offset.')
        return d.astimezone(timezone.utc).isoformat(timespec='milliseconds')
    @model_validator(mode='after')
    def order(self):
        if self.end<=self.start:raise ValueError('End must follow start.')
        return self
class TimerReview(Review):
    intervals:list[Interval]|None=Field(default=None,max_length=100)
class SourceLine(Line):
    source_kind:Literal['field','time','material']|None=None
    source_id:Identity|None=None
    source_version:int|None=Field(default=None,strict=True,ge=1)
    @model_validator(mode='after')
    def bound(self):
        if any(x is not None for x in (self.source_kind,self.source_id,self.source_version)) and any(x is None for x in (self.source_kind,self.source_id,self.source_version)):raise ValueError('Select an exact source and version.')
        return self
class DraftReview(Strict):
    kind:Literal['quote','invoice']='invoice'
    document_id:Identity|None=None
    version:int|None=Field(default=None,strict=True,ge=1)
    lines:list[SourceLine]=Field(min_length=1,max_length=40)
    vat_percent:Literal[0,5,20]=0
    notes:str=Field(default='',max_length=2000)
    confirmed:bool=Field(strict=True)
    @model_validator(mode='after')
    def consent(self):
        if not self.confirmed:raise ValueError('Review source quantities and enter customer prices explicitly.')
        if (self.document_id is None)!=(self.version is None):raise ValueError('Choose the draft and its version together.')
        keys=[(i.source_kind,i.source_id) for i in self.lines if i.source_id]
        if len(set(keys))!=len(keys):raise ValueError('Select each source once.')
        return self