"""Local persistence. Each business has its own SQLite database and assets.

The registry holds only identity, sessions, rate limits and demo host mappings.
Tenant data access takes an authenticated registry business ID, never a client
supplied database path. This prototype decision is not the hosting decision.
"""
from contextlib import contextmanager
from pathlib import Path
import hashlib
import json
import secrets
import re
import shutil
import sqlite3
from datetime import datetime, timezone
from .data_lock import data_lock


DEMO_BUSINESSES = (
    {'id': 'oak', 'slug': 'oak-and-vale', 'name': 'Oak & Vale', 'email': 'owner@oak.example', 'password': 'Oak-demo-only-2026!', 'colour': '#245648', 'area': 'The valleys and surrounding villages'},
    {'id': 'harbour', 'slug': 'harbour-homeworks', 'name': 'Harbour Homeworks', 'email': 'owner@harbour.example', 'password': 'Harbour-demo-only-2026!', 'colour': '#254f76', 'area': 'The coast and surrounding towns'},
)


def now():
    return datetime.now(timezone.utc).isoformat(timespec='seconds')


def uid():
    return secrets.token_hex(16)


def digest(value):
    return hashlib.sha256(value.encode()).hexdigest()


def password_hash(value, salt=None):
    salt = salt or secrets.token_hex(16)
    encoded = hashlib.scrypt(value.encode(), salt=bytes.fromhex(salt), n=16384, r=8, p=1).hex()
    return salt + ':' + encoded


def password_matches(value, stored):
    valid=isinstance(stored,str) and re.fullmatch(r'[a-f0-9]{32}:[a-f0-9]{128}',stored)
    calculated=password_hash(value,stored.split(':')[0] if valid else '00'*16)
    return bool(valid) and secrets.compare_digest(calculated,stored)


def initial_site(b):
    return {
        'business_name': b['name'], 'tagline': 'Small jobs. Thoughtfully finished.',
        'intro': 'A friendly, practical approach to caring for your home. Tell us what needs doing and we will help you plan the next step.',
        'about': 'This is an editable website for a fictional local home maintenance business. Replace this introduction with your own story, skills and way of working before a real launch.',
        'area': b['area'], 'email': b['email'].replace('owner@', 'hello@'), 'phone': '',
        'colour': b['colour'], 'logo_id': None, 'gallery': [],
        'services': [
            {'title': 'Repairs & maintenance', 'description': 'Help with the small repairs and useful improvements that make your home work better.'},
            {'title': 'Carpentry & fitting', 'description': 'Shelves, doors, storage and careful finishing touches, planned around your space.'},
            {'title': 'Home improvements', 'description': 'Practical support for your next project, with a clear scope and a written quote.'},
        ],
    }


REGISTRY_SCHEMA = '''
CREATE TABLE IF NOT EXISTS businesses(id TEXT PRIMARY KEY, slug TEXT UNIQUE NOT NULL, name TEXT NOT NULL, enabled INTEGER NOT NULL DEFAULT 1);
CREATE TABLE IF NOT EXISTS users(id TEXT PRIMARY KEY, business_id TEXT NOT NULL REFERENCES businesses(id), email TEXT UNIQUE NOT NULL, password TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS sessions(token TEXT PRIMARY KEY, business_id TEXT NOT NULL REFERENCES businesses(id), subject TEXT NOT NULL, kind TEXT NOT NULL, csrf TEXT NOT NULL, expires REAL NOT NULL, portal_version INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS limits(key TEXT PRIMARY KEY, count INTEGER NOT NULL, expires REAL NOT NULL);
CREATE TABLE IF NOT EXISTS domains(hostname TEXT PRIMARY KEY, business_id TEXT NOT NULL REFERENCES businesses(id));
CREATE TABLE IF NOT EXISTS dataset_identity(id INTEGER PRIMARY KEY CHECK(id=1), dataset_id TEXT NOT NULL);
'''

TENANT_SCHEMA = '''
CREATE TABLE IF NOT EXISTS customers(id TEXT PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, phone TEXT NOT NULL, address TEXT NOT NULL, portal_hash TEXT, portal_version INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS jobs(id TEXT PRIMARY KEY, customer_id TEXT NOT NULL REFERENCES customers(id), service TEXT NOT NULL, message TEXT NOT NULL, status TEXT NOT NULL, notes TEXT NOT NULL DEFAULT '', scheduled_date TEXT NOT NULL DEFAULT '', version INTEGER NOT NULL DEFAULT 1, source TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS jobs_customer ON jobs(customer_id);
CREATE TABLE IF NOT EXISTS quotes(id TEXT PRIMARY KEY, reference TEXT UNIQUE NOT NULL, job_id TEXT NOT NULL REFERENCES jobs(id), customer_id TEXT NOT NULL REFERENCES customers(id), status TEXT NOT NULL, snapshot TEXT NOT NULL, total_pence INTEGER NOT NULL, version INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS invoices(id TEXT PRIMARY KEY, reference TEXT UNIQUE NOT NULL, quote_id TEXT UNIQUE NOT NULL REFERENCES quotes(id), job_id TEXT NOT NULL REFERENCES jobs(id), customer_id TEXT NOT NULL REFERENCES customers(id), snapshot TEXT NOT NULL, total_pence INTEGER NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS payments(id TEXT PRIMARY KEY, invoice_id TEXT NOT NULL REFERENCES invoices(id), amount_pence INTEGER NOT NULL CHECK(amount_pence > 0), note TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS operations(key TEXT PRIMARY KEY, fingerprint TEXT NOT NULL, result TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS assets(id TEXT PRIMARY KEY, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS site(id INTEGER PRIMARY KEY CHECK(id=1), draft TEXT NOT NULL, published TEXT NOT NULL, version INTEGER NOT NULL, published_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS site_history(id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS audit(id INTEGER PRIMARY KEY AUTOINCREMENT, action TEXT NOT NULL, subject TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS storage_identity(id INTEGER PRIMARY KEY CHECK(id=1), business_id TEXT NOT NULL, dataset_id TEXT NOT NULL);
'''


class Store:
    def __init__(self, root, *, seed_demo=True):
        self.root = Path(root).resolve()
        self.root.mkdir(parents=True, exist_ok=True, mode=0o700)
        self.root.chmod(0o700)
        with data_lock(self.root):
            if (self.root/'RESTORE_IN_PROGRESS').exists():
                raise ValueError('This recovery folder is incomplete. Restore again into a fresh folder.')
            self._initialise(seed_demo)

    def _initialise(self,seed_demo):
        from .identity_schema import setup_schema,migrate_members
        with self.registry() as db:
            db.executescript(REGISTRY_SCHEMA)
            fresh=db.execute('SELECT 1 FROM businesses').fetchone() is None and db.execute('SELECT 1 FROM dataset_identity').fetchone() is None
            setup_schema(db)
            db.execute('INSERT OR IGNORE INTO dataset_identity VALUES(1,?)',(uid(),))
            dataset_id=db.execute('SELECT dataset_id FROM dataset_identity WHERE id=1').fetchone()[0]
            if fresh and seed_demo:
                for b in DEMO_BUSINESSES:
                    db.execute('INSERT INTO businesses(id,slug,name) VALUES(?,?,?)',(b['id'],b['slug'],b['name']))
                    db.execute('INSERT INTO users(id,business_id,email,password,display_name) VALUES(?,?,?,?,?)',(b['id'],b['id'],b['email'],password_hash(b['password']),'Local demo owner'))
            migrate_members(db)
            self._clean_provisioning(db,dataset_id)
            businesses=many(db,'SELECT * FROM businesses ORDER BY id')
        for b in businesses:
            if not valid_business_id(b['id']):raise ValueError('Unsupported local business identity.')
            folder=self.root/b['id']
            if folder.is_symlink() or (folder/'business.sqlite3').is_symlink() or (folder/'assets').is_symlink() or (folder/'photos').is_symlink():raise ValueError('Business folders must not use symlinks.')
            if not fresh and not (folder/'business.sqlite3').is_file():raise ValueError('Registered business storage is missing. Recover into a fresh folder.')
            legacy=next((d for d in DEMO_BUSINESSES if d['id']==b['id']),None)
            template=legacy or {**b,'area':'Add your service area','email':'','colour':'#245648'}
            self.initialise_folder(folder,template,dataset_id)

    def initialise_folder(self,folder,b,dataset_id):
        folder=Path(folder)
        if folder.is_symlink() or (folder/'business.sqlite3').is_symlink() or (folder/'assets').is_symlink() or (folder/'photos').is_symlink():raise ValueError('Unsafe business storage path.')
        folder.mkdir(exist_ok=True,mode=0o700)
        (folder/'assets').mkdir(exist_ok=True,mode=0o700)
        (folder/'photos').mkdir(exist_ok=True,mode=0o700)
        with self.connection(folder/'business.sqlite3') as db:
            db.executescript(TENANT_SCHEMA)
            existing=db.execute('SELECT business_id,dataset_id FROM storage_identity WHERE id=1').fetchone()
            if existing and tuple(existing)!=(b['id'],dataset_id):raise ValueError('Business data belongs to a different folder or dataset.')
            from .documents import setup as setup_documents
            setup_documents(db)
            db.execute('INSERT OR IGNORE INTO storage_identity VALUES(1,?,?)',(b['id'],dataset_id))
            from .customers import setup as setup_customers
            setup_customers(db)
            from .materials import SCHEMA as MATERIALS_SCHEMA
            from .workforce import SCHEMA as WORKFORCE_SCHEMA
            from .scheduling import SCHEMA as SCHEDULING_SCHEMA
            from .identity_schema import TENANT_SCHEMA as IDENTITY_TENANT_SCHEMA
            from .photo_models import SCHEMA as PHOTO_SCHEMA
            from .field_models import SCHEMA as FIELD_SCHEMA
            from .recurrence_models import SCHEMA as RECURRENCE_SCHEMA
            db.executescript(MATERIALS_SCHEMA+WORKFORCE_SCHEMA+SCHEDULING_SCHEMA+IDENTITY_TENANT_SCHEMA+PHOTO_SCHEMA+FIELD_SCHEMA+RECURRENCE_SCHEMA)
            content=json.dumps(initial_site(b))
            db.execute('INSERT OR IGNORE INTO site VALUES(1,?,?,1,?)',(content,content,now()))
            if not db.execute('SELECT 1 FROM site_history').fetchone():db.execute('INSERT INTO site_history(content,created_at) VALUES(?,?)',(content,now()))
            from .websites import setup as setup_websites
            setup_websites(db)
            from .portal_access import setup as setup_portal
            from .sales_outputs import setup as setup_sales
            setup_portal(db)
            setup_sales(db)
            from .feedback_models import SCHEMA as FEEDBACK_SCHEMA
            db.executescript(FEEDBACK_SCHEMA)
            from .workflow import setup as setup_workflow
            setup_workflow(db)
            from .customer_review import setup as setup_customer_review
            setup_customer_review(db)
            from .public_enquiries import SCHEMA as ENQUIRY_SCHEMA
            db.executescript(ENQUIRY_SCHEMA)

    def _clean_provisioning(self,db,dataset_id):
        pending=self.root/'.provisioning'
        if not pending.exists():return
        if pending.is_symlink():raise ValueError('Unsafe provisioning folder.')
        for marker in pending.glob('*.json'):
            if marker.is_symlink() or not re.fullmatch(r'[a-f0-9]{32}\.json',marker.name):raise ValueError('Unsafe provisioning marker.')
            data=json.loads(marker.read_text());bid=marker.stem
            if data!={'business_id':bid,'dataset_id':dataset_id}:raise ValueError('Unrecognised provisioning marker.')
            registered=db.execute('SELECT 1 FROM businesses WHERE id=?',(bid,)).fetchone()
            if not registered:
                final=self.root/bid
                if final.exists():
                    if final.is_symlink():raise ValueError('Unsafe incomplete business folder.')
                    with self.connection(final/'business.sqlite3') as tenant:
                        identity=tenant.execute('SELECT business_id,dataset_id FROM storage_identity WHERE id=1').fetchone()
                    if not identity or tuple(identity)!=(bid,dataset_id):raise ValueError('Incomplete business identity mismatch.')
                    shutil.rmtree(final)
                stage=pending/bid
                if stage.exists():
                    if stage.is_symlink():raise ValueError('Unsafe staging folder.')
                    shutil.rmtree(stage)
            marker.unlink()

    @contextmanager
    def connection(self, path):
        db = sqlite3.connect(path, timeout=30)
        db.row_factory = sqlite3.Row
        db.execute('PRAGMA foreign_keys = ON')
        try:
            yield db
            db.commit()
        except BaseException:
            db.rollback()
            raise
        finally:
            db.close()

    def registry(self):
        return self.connection(self.root / 'registry.sqlite3')

    def tenant(self, business_id):
        folder=self.business_folder(business_id)
        if not (folder/'business.sqlite3').is_file():raise ValueError('Business storage is incomplete.')
        return self.connection(folder/'business.sqlite3')

    def business_folder(self,business_id):
        if not valid_business_id(business_id):raise ValueError('Invalid local business identity.')
        with self.registry() as db:
            if not db.execute('SELECT 1 FROM businesses WHERE id=?',(business_id,)).fetchone():raise ValueError('Unknown local business.')
        folder=self.root/business_id
        if folder.is_symlink() or (folder/'business.sqlite3').is_symlink() or (folder/'assets').is_symlink() or (folder/'photos').is_symlink():raise ValueError('Business storage must not use symlinks.')
        return folder

    def asset_path(self,business_id,asset_id):
        if not isinstance(asset_id,str) or not re.fullmatch(r'[a-f0-9]{32}',asset_id):raise ValueError('Invalid asset identity.')
        path=self.business_folder(business_id)/'assets'/(asset_id+'.png')
        if path.is_symlink():raise ValueError('Asset files must not use symlinks.')
        return path

    def photo_path(self,business_id,blob_id):
        if not isinstance(blob_id,str) or not re.fullmatch(r'[a-f0-9]{32}',blob_id):raise ValueError('Invalid private image identity.')
        path=self.business_folder(business_id)/'photos'/(blob_id+'.png')
        if path.is_symlink():raise ValueError('Private images must not use symlinks.')
        return path

def valid_business_id(value):
    return isinstance(value,str) and (value in ('oak','harbour') or re.fullmatch(r'[a-f0-9]{32}',value) is not None)


def audit(db, action, subject):
    db.execute('INSERT INTO audit(action,subject,created_at) VALUES(?,?,?)', (action, subject, now()))


def one(db, sql, args=()):
    row = db.execute(sql, args).fetchone()
    return dict(row) if row else None


def many(db, sql, args=()):
    return [dict(row) for row in db.execute(sql, args).fetchall()]