"""Manual Materials, isolated to the authenticated staff business.

No supplier clients, OCR, AI, source imports or background workers. Catalogue
charge prices are reference-only: this module never writes financial records.
"""
from datetime import date
import json
import sqlite3
from fastapi import HTTPException, Query, Request
from . import material_rules as m
from .store import uid, now, one, many, audit


SCHEMA = '''
CREATE TABLE IF NOT EXISTS materials_catalogue(
 id TEXT PRIMARY KEY, name TEXT NOT NULL, name_key TEXT NOT NULL,
 supplier TEXT NOT NULL, supplier_sku TEXT NOT NULL, unit TEXT NOT NULL,
 default_cost_pence INTEGER NOT NULL CHECK(default_cost_pence >= 0),
 default_charge_pence INTEGER CHECK(default_charge_pence >= 0),
 archived INTEGER NOT NULL DEFAULT 0, version INTEGER NOT NULL DEFAULT 1,
 created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE UNIQUE INDEX IF NOT EXISTS catalogue_active_name ON materials_catalogue(name_key) WHERE archived=0;
CREATE TABLE IF NOT EXISTS job_materials(
 id TEXT PRIMARY KEY, job_id TEXT NOT NULL REFERENCES jobs(id),
 name TEXT NOT NULL, quantity TEXT NOT NULL, unit TEXT NOT NULL,
 cost_pence INTEGER NOT NULL CHECK(cost_pence >= 0), total_pence INTEGER NOT NULL CHECK(total_pence >= 0),
 supplier TEXT NOT NULL, supplier_sku TEXT NOT NULL, notes TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('to_order','ordered','received')),
 ordered_date TEXT NOT NULL, received_date TEXT NOT NULL, supplier_links TEXT NOT NULL,
 catalogue_id TEXT REFERENCES materials_catalogue(id), catalogue_snapshot TEXT,
 archived INTEGER NOT NULL DEFAULT 0, version INTEGER NOT NULL DEFAULT 1,
 created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS materials_job ON job_materials(job_id);
CREATE TABLE IF NOT EXISTS material_audit(
 id INTEGER PRIMARY KEY AUTOINCREMENT, record_id TEXT NOT NULL, kind TEXT NOT NULL,
 action TEXT NOT NULL, actor TEXT NOT NULL, version INTEGER NOT NULL,
 before_value TEXT, after_value TEXT, created_at TEXT NOT NULL);
'''

JOIN = '''SELECT m.*,j.customer_id,j.service,j.status AS job_status,c.name AS customer_name
 FROM job_materials m JOIN jobs j ON j.id=m.job_id JOIN customers c ON c.id=j.customer_id'''


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 has changed. Reload Materials before trying again.')
    if active and row['archived']:
        raise HTTPException(409, 'Restore this archived record before changing or using it.')
    return row


def decoded(row):
    if row:
        row['supplier_links'] = json.loads(row['supplier_links'])
        row['catalogue_snapshot'] = json.loads(row['catalogue_snapshot']) if row['catalogue_snapshot'] else None
    return row


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


def dates(status, ordered='', received=''):
    today = date.today().isoformat()
    if status == 'received':
        received = received or today
        ordered = ordered or received
    if status == 'ordered':
        ordered = ordered or today
    if ordered and received and ordered > received:
        raise HTTPException(422, 'Received date cannot be before ordered date.')
    return ordered, received


def insert_material(db, actor, job_id, payload, catalogue=None):
    required(one(db, 'SELECT id FROM jobs WHERE id=?', (job_id,)))
    mid, stamp = uid(), now()
    cost = m.price(payload.cost_price)
    ordered, received = dates(payload.status, payload.ordered_date, payload.received_date)
    snapshot = None
    if catalogue:
        snapshot = {k: catalogue[k] for k in ('id', 'name', 'supplier', 'supplier_sku', 'unit', 'default_cost_pence', 'default_charge_pence', 'version')}
        snapshot['snapped_at'] = stamp
    values = {
        'id': mid, 'job_id': job_id, 'name': payload.name, 'quantity': payload.quantity,
        'unit': payload.unit, 'cost_pence': cost, 'total_pence': m.total_pence(payload.quantity, cost),
        'supplier': payload.supplier, 'supplier_sku': payload.supplier_sku, 'notes': payload.notes,
        'status': payload.status, 'ordered_date': ordered, 'received_date': received,
        'supplier_links': json.dumps([x.model_dump() for x in payload.supplier_links]),
        'catalogue_id': catalogue['id'] if catalogue else None,
        'catalogue_snapshot': json.dumps(snapshot) if snapshot else None, 'created_at': stamp, 'updated_at': stamp,
    }
    db.execute('INSERT INTO job_materials(' + ','.join(values) + ') VALUES(' + ','.join('?' for _ in values) + ')', tuple(values.values()))
    row = decoded(one(db, 'SELECT * FROM job_materials WHERE id=?', (mid,)))
    log(db, actor, 'tracker', 'created', None, row)
    return row


def mount_materials(app, store, session, once):
    @app.get('/api/materials')
    def materials(request: Request, job_id: str = '', customer_id: str = '', status: str = '', supplier: str = '', q: str = Query(default='', max_length=180), historical: bool = False, archived: bool = False):
        s = session(request)
        with store.tenant(s['business_id']) as db:
            if job_id:
                required(one(db, 'SELECT id FROM jobs WHERE id=?', (job_id,)))
            if customer_id:
                required(one(db, 'SELECT id FROM customers WHERE id=?', (customer_id,)))
            if status and status not in ('to_order', 'ordered', 'received'):
                raise HTTPException(422, 'Unknown material stage.')
            clauses, args = ['m.archived=?'], [int(archived)]
            for key, value in (('m.job_id', job_id), ('j.customer_id', customer_id), ('m.status', status), ('m.supplier', supplier)):
                if value:
                    clauses.append(key + '=?'); args.append(value)
            # A job's own panel always includes its historical materials.
            if not historical and not job_id:
                clauses.append("j.status NOT IN ('completed','paid','cancelled')")
            rows = [decoded(x) for x in many(db, JOIN + ' WHERE ' + ' AND '.join(clauses) + ' ORDER BY m.created_at DESC,m.rowid DESC', args)]
            if q:
                needle = q.casefold()
                rows = [r for r in rows if needle in ' '.join(str(r[k]) for k in ('name','supplier','supplier_sku','notes','service','customer_name')).casefold()]
            suppliers = [r['supplier'] for r in many(db, "SELECT DISTINCT supplier FROM job_materials WHERE supplier!='' ORDER BY supplier")]
        summary = {stage: {'count': sum(r['status'] == stage for r in rows), 'cost_pence': sum(r['total_pence'] for r in rows if r['status'] == stage)} for stage in ('to_order', 'ordered', 'received')}
        return {'items': rows, 'summary': summary, 'total_pence': sum(r['total_pence'] for r in rows), 'suppliers': suppliers}

    @app.get('/api/materials/history-match')
    def history(request: Request, name: str = Query(min_length=1, max_length=180)):
        s = session(request)
        with store.tenant(s['business_id']) as db:
            # Exact trim/case-insensitive name; never a customer-name match.
            rows = many(db, 'SELECT name,cost_pence,supplier,unit,created_at FROM job_materials WHERE archived=0 AND cost_pence>0 ORDER BY created_at DESC,rowid DESC')
            return next((r for r in rows if r['name'].strip().casefold() == name.strip().casefold()), None)

    @app.get('/api/materials/order')
    def supplier_order(request: Request, ids: str = Query(min_length=1, max_length=6600)):
        s = session(request)
        wanted = ids.split(',')
        if len(wanted) > 200 or len(set(wanted)) != len(wanted):
            raise HTTPException(422, 'Choose between 1 and 200 different material rows.')
        with store.tenant(s['business_id']) as db:
            rows = many(db, JOIN + ' WHERE m.archived=0 AND m.id IN (' + ','.join('?' for _ in wanted) + ')', wanted)
            if len(rows) != len(wanted):
                raise HTTPException(404, 'One or more materials are unavailable in this business.')
            # A narrow projection: never send cost, charge, notes, links or snapshots.
            result = [{k: r[k] for k in ('name','quantity','unit','supplier','supplier_sku','job_id','service')} for r in rows]
        return {'business_name': s['name'], 'created_at': now(), 'items': sorted(result, key=lambda r: (r['supplier'].casefold(), r['name'].casefold()))}

    @app.post('/api/materials')
    def create(payload: m.MaterialCreate, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            return once(db, request, payload.model_dump(), lambda: insert_material(db, s['subject'], payload.job_id, payload))

    @app.post('/api/materials/import')
    def import_csv(payload: m.CsvImport, 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,)))
                rows = [insert_material(db, s['subject'], payload.job_id, row) for row in payload.rows]
                return {'count': len(rows), 'ids': [r['id'] for r in rows], 'total_pence': sum(r['total_pence'] for r in rows)}
            return once(db, request, payload.model_dump(), action)

    @app.post('/api/materials/bulk-stage')
    def bulk(payload: m.BulkStage, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                rows = [current(one(db, 'SELECT * FROM job_materials WHERE id=?', (i.id,)), i.version) for i in payload.items]
                for row in rows:
                    ordered, received = dates(payload.status, row['ordered_date'], row['received_date'])
                    db.execute('UPDATE job_materials SET status=?,ordered_date=?,received_date=?,updated_at=?,version=version+1 WHERE id=?', (payload.status, ordered, received, now(), row['id']))
                    log(db, s['subject'], 'tracker', 'stage_changed', decoded(row), decoded(one(db, 'SELECT * FROM job_materials WHERE id=?', (row['id'],))))
                return {'count': len(rows)}
            return once(db, request, payload.model_dump(), action)

    @app.put('/api/materials/{material_id}')
    def edit(material_id: str, payload: m.MaterialEdit, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                row = current(one(db, 'SELECT * FROM job_materials WHERE id=?', (material_id,)), payload.version)
                ordered, received = dates(payload.status, payload.ordered_date, payload.received_date)
                cost = m.price(payload.cost_price)
                db.execute('UPDATE job_materials SET name=?,quantity=?,unit=?,cost_pence=?,total_pence=?,supplier=?,supplier_sku=?,notes=?,status=?,ordered_date=?,received_date=?,supplier_links=?,updated_at=?,version=version+1 WHERE id=?',
                           (payload.name, payload.quantity, payload.unit, cost, m.total_pence(payload.quantity, cost), payload.supplier, payload.supplier_sku, payload.notes, payload.status, ordered, received, json.dumps([r.model_dump() for r in payload.supplier_links]), now(), material_id))
                after = decoded(one(db, 'SELECT * FROM job_materials WHERE id=?', (material_id,)))
                log(db, s['subject'], 'tracker', 'edited', decoded(row), after)
                return after
            return once(db, request, payload.model_dump(), action)

    @app.post('/api/materials/{material_id}/{action}')
    def archive_material(material_id: str, action: str, payload: m.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 job_materials WHERE id=?', (material_id,)), payload.version, active=False)
                target = int(action == 'archive')
                if row['archived'] == target:
                    raise HTTPException(409, 'This material is already in that state.')
                db.execute('UPDATE job_materials SET archived=?,version=version+1,updated_at=? WHERE id=?', (target, now(), material_id))
                after = decoded(one(db, 'SELECT * FROM job_materials WHERE id=?', (material_id,)))
                log(db, s['subject'], 'tracker', action, decoded(row), after)
                return after
            return once(db, request, payload.model_dump(), change)

    @app.get('/api/catalogue')
    def catalogue(request: Request, archived: bool = False, q: str = Query(default='', max_length=180)):
        s = session(request)
        with store.tenant(s['business_id']) as db:
            rows = many(db, 'SELECT * FROM materials_catalogue WHERE archived=? ORDER BY name COLLATE NOCASE', (int(archived),))
            return [r for r in rows if not q or q.casefold() in ' '.join(r[k] for k in ('name', 'supplier', 'supplier_sku', 'unit')).casefold()]

    @app.post('/api/catalogue')
    def catalogue_create(payload: m.CatalogueFields, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                cid, stamp = uid(), now()
                try:
                    db.execute('INSERT INTO materials_catalogue(id,name,name_key,supplier,supplier_sku,unit,default_cost_pence,default_charge_pence,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?)',
                               (cid, payload.name, payload.name.lower(), payload.supplier, payload.supplier_sku, payload.unit, m.price(payload.cost_price), m.price(payload.charge_price) if payload.charge_price else None, stamp, stamp))
                except sqlite3.IntegrityError:
                    raise HTTPException(409, 'An active catalogue item already has that name.')
                row = one(db, 'SELECT * FROM materials_catalogue WHERE id=?', (cid,))
                log(db, s['subject'], 'catalogue', 'created', None, row)
                return row
            return once(db, request, payload.model_dump(), action)

    @app.put('/api/catalogue/{catalogue_id}')
    def catalogue_edit(catalogue_id: str, payload: m.CatalogueEdit, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                row = current(one(db, 'SELECT * FROM materials_catalogue WHERE id=?', (catalogue_id,)), payload.version)
                try:
                    db.execute('UPDATE materials_catalogue SET name=?,name_key=?,supplier=?,supplier_sku=?,unit=?,default_cost_pence=?,default_charge_pence=?,updated_at=?,version=version+1 WHERE id=?',
                               (payload.name, payload.name.lower(), payload.supplier, payload.supplier_sku, payload.unit, m.price(payload.cost_price), m.price(payload.charge_price) if payload.charge_price else None, now(), catalogue_id))
                except sqlite3.IntegrityError:
                    raise HTTPException(409, 'An active catalogue item already has that name.')
                after = one(db, 'SELECT * FROM materials_catalogue WHERE id=?', (catalogue_id,))
                log(db, s['subject'], 'catalogue', 'edited', row, after)
                return after
            return once(db, request, payload.model_dump(), action)

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

    @app.post('/api/catalogue/{catalogue_id}/add-to-job')
    def catalogue_add(catalogue_id: str, payload: m.CatalogueAdd, request: Request):
        s = session(request, csrf=True)
        with store.tenant(s['business_id']) as db:
            def action():
                item = current(one(db, 'SELECT * FROM materials_catalogue WHERE id=?', (catalogue_id,)), payload.version)
                cost = payload.cost_override or f"{item['default_cost_pence'] // 100}.{item['default_cost_pence'] % 100:02}"
                fields = m.MaterialFields(name=item['name'], quantity=payload.quantity, unit=item['unit'], cost_price=cost, supplier=item['supplier'], supplier_sku=item['supplier_sku'], notes=payload.notes)
                row = insert_material(db, s['subject'], payload.job_id, fields, item)
                log(db, s['subject'], 'catalogue', 'added_to_job', item, item)
                return row
            return once(db, request, payload.model_dump(), action)

    @app.post('/api/catalogue/{catalogue_id}/{action}')
    def catalogue_archive(catalogue_id: str, action: str, payload: m.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 materials_catalogue WHERE id=?', (catalogue_id,)), payload.version, active=False)
                target = int(action == 'archive')
                if row['archived'] == target:
                    raise HTTPException(409, 'This catalogue item is already in that state.')
                try:
                    db.execute('UPDATE materials_catalogue SET archived=?,version=version+1,updated_at=? WHERE id=?', (target, now(), catalogue_id))
                except sqlite3.IntegrityError:
                    raise HTTPException(409, 'Rename the other active item before restoring this name.')
                after = one(db, 'SELECT * FROM materials_catalogue WHERE id=?', (catalogue_id,))
                log(db, s['subject'], 'catalogue', action, row, after)
                return after
            return once(db, request, payload.model_dump(), change)