"""Owner-only operational metadata; never part of customer projections."""
from typing import Literal
from datetime import date
import re
from pydantic import Field, StrictBool, StrictInt, field_validator, model_validator
from .models import Strict, JobEdit

SCHEMA='''
CREATE TABLE IF NOT EXISTS job_workflow(job_id TEXT PRIMARY KEY REFERENCES jobs(id),content TEXT NOT NULL,feedback_version INTEGER NOT NULL,version INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS job_workflow_history(id INTEGER PRIMARY KEY AUTOINCREMENT,job_id TEXT NOT NULL REFERENCES jobs(id),version INTEGER NOT NULL,job_version INTEGER NOT NULL,actor TEXT NOT NULL,snapshot TEXT NOT NULL,created_at TEXT NOT NULL,UNIQUE(job_id,version));
CREATE TABLE IF NOT EXISTS opportunity_settings(id INTEGER PRIMARY KEY CHECK(id=1),content TEXT NOT NULL,version INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS opportunity_settings_history(version INTEGER PRIMARY KEY,content TEXT NOT NULL,actor TEXT NOT NULL,created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS opportunity_reviews(id TEXT PRIMARY KEY,kind TEXT NOT NULL,job_id TEXT NOT NULL REFERENCES jobs(id),customer_id TEXT NOT NULL REFERENCES customers(id),quote_id TEXT REFERENCES quotes(id),generation TEXT NOT NULL,state TEXT NOT NULL,snooze_until TEXT NOT NULL,draft TEXT NOT NULL,note TEXT NOT NULL,context TEXT NOT NULL,version INTEGER NOT NULL,updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS opportunity_history(id INTEGER PRIMARY KEY AUTOINCREMENT,opportunity_id TEXT NOT NULL REFERENCES opportunity_reviews(id),version INTEGER NOT NULL,action TEXT NOT NULL,actor TEXT NOT NULL,snapshot TEXT NOT NULL,created_at TEXT NOT NULL,UNIQUE(opportunity_id,version));
'''

def calendar_day(value):
    if value and (not re.fullmatch(r'\d{4}-\d{2}-\d{2}',value) or date.fromisoformat(value).isoformat()!=value):raise ValueError('Use a valid YYYY-MM-DD date.')
    return value

def tags(value):
    values=[s.strip().lower() for s in value]
    if any(not re.fullmatch(r'[a-z0-9][a-z0-9 _-]{0,39}',s) for s in values):raise ValueError('Tags use letters, numbers, spaces, underscores or hyphens.')
    if len(set(values))!=len(values):raise ValueError('Use each tag once.')
    return sorted(values)

class Metadata(Strict):
    priority:Literal['low','normal','high','urgent']='normal'
    tags:list[str]=Field(default_factory=list,max_length=12)
    next_action:str=Field(default='',max_length=300)
    next_action_date:str=''
    last_contact_date:str=''
    completed_date:str=''
    postcode_group:str=Field(default='',max_length=4)
    archived:StrictBool=False
    followup_excluded:StrictBool=False
    @field_validator('tags')
    @classmethod
    def valid_tags(cls,value):return tags(value)
    @field_validator('next_action_date','last_contact_date','completed_date')
    @classmethod
    def valid_day(cls,value):return calendar_day(value)
    @field_validator('postcode_group')
    @classmethod
    def outward_code(cls,value):
        value=value.upper().strip()
        if value and not re.fullmatch(r'[A-Z]{1,2}[0-9][A-Z0-9]?',value):raise ValueError('Use an explicitly supplied postcode outward code, or leave blank.')
        return value
    @model_validator(mode='after')
    def action_date(self):
        self.next_action=self.next_action.strip()
        if self.next_action_date and not self.next_action:raise ValueError('A next-action date needs an action.')
        return self

class WorkflowEdit(JobEdit):
    version:StrictInt=Field(ge=1)
    metadata:Metadata
    @field_validator('scheduled_date')
    @classmethod
    def valid_target_day(cls,value):return calendar_day(value)

class Settings(Strict):
    quote_days:StrictInt=Field(default=7,ge=1,le=365)
    repeat_days:StrictInt=Field(default=180,ge=1,le=1825)
    nearby_enabled:StrictBool=False
    nearby_horizon_days:StrictInt=Field(default=14,ge=1,le=90)
    excluded_tags:list[str]=Field(default_factory=list,max_length=12)
    @field_validator('excluded_tags')
    @classmethod
    def valid_tags(cls,value):return tags(value)

class SettingsEdit(Settings):
    version:StrictInt=Field(ge=1)

class Review(Strict):
    version:StrictInt=Field(ge=0)
    context_hash:str=Field(pattern=r'^[a-f0-9]{64}$')
    action:Literal['prepare','snooze','dismiss','complete','reopen']
    confirmed:StrictBool
    snooze_until:str=''
    draft:str=Field(default='',max_length=3000)
    note:str=Field(default='',max_length=1000)
    @field_validator('snooze_until')
    @classmethod
    def valid_day(cls,value):return calendar_day(value)
    @model_validator(mode='after')
    def deliberate(self):
        if not self.confirmed:raise ValueError('Review the exact item before recording a decision.')
        if self.action=='prepare' and len(self.draft.strip())<5:raise ValueError('Prepare a local draft first.')
        if self.action in ('snooze','dismiss','complete') and len(self.note.strip())<5:raise ValueError('Record a short reason or the action you manually took.')
        if self.action!='snooze' and self.snooze_until:raise ValueError('Only snooze uses a date.')
        if self.action!='prepare' and self.draft:raise ValueError('Only prepare changes the draft.')
        return self