"""Versioned registry identity contracts, separate from business branding."""
import secrets
from datetime import datetime,timezone

SCHEMA='''
CREATE TABLE IF NOT EXISTS registry_meta(key TEXT PRIMARY KEY,value TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS memberships(
 id TEXT PRIMARY KEY,business_id TEXT NOT NULL REFERENCES businesses(id),user_id TEXT NOT NULL REFERENCES users(id),
 role TEXT NOT NULL CHECK(role IN ('owner','staff')),enabled INTEGER NOT NULL DEFAULT 1 CHECK(enabled IN (0,1)),
 version INTEGER NOT NULL DEFAULT 1,resource_id TEXT,created_at TEXT NOT NULL,UNIQUE(business_id,user_id));
CREATE UNIQUE INDEX IF NOT EXISTS membership_resource ON memberships(business_id,resource_id) WHERE resource_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS access_tokens(
 id TEXT PRIMARY KEY,token_hash TEXT UNIQUE NOT NULL,kind TEXT NOT NULL CHECK(kind IN ('invite','reset')),
 business_id TEXT REFERENCES businesses(id),user_id TEXT REFERENCES users(id),email TEXT NOT NULL,
 role TEXT CHECK(role IN ('owner','staff')),resource_id TEXT,expires REAL NOT NULL,used INTEGER NOT NULL DEFAULT 0 CHECK(used IN (0,1)),
 created_at TEXT NOT NULL,created_by TEXT REFERENCES users(id));
CREATE TABLE IF NOT EXISTS registry_operations(key TEXT NOT NULL,user_id TEXT NOT NULL REFERENCES users(id),fingerprint TEXT NOT NULL,result TEXT NOT NULL,PRIMARY KEY(key,user_id));
CREATE TABLE IF NOT EXISTS identity_audit(id INTEGER PRIMARY KEY AUTOINCREMENT,business_id TEXT REFERENCES businesses(id),actor_id TEXT,target_id TEXT NOT NULL,action TEXT NOT NULL,detail TEXT NOT NULL,created_at TEXT NOT NULL);
'''
TENANT_SCHEMA='''
CREATE TABLE IF NOT EXISTS setup_progress(id INTEGER PRIMARY KEY CHECK(id=1),version INTEGER NOT NULL DEFAULT 1,reviewed_publication_id INTEGER,solo_confirmed INTEGER NOT NULL DEFAULT 0 CHECK(solo_confirmed IN (0,1)),guide_read INTEGER NOT NULL DEFAULT 0 CHECK(guide_read IN (0,1)));
INSERT OR IGNORE INTO setup_progress(id) VALUES(1);
CREATE TABLE IF NOT EXISTS staff_updates(id TEXT PRIMARY KEY,job_id TEXT NOT NULL REFERENCES jobs(id),membership_id TEXT NOT NULL,author_id TEXT NOT NULL,resource_id TEXT NOT NULL REFERENCES team_resources(id),note TEXT NOT NULL,created_at TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS staff_updates_job ON staff_updates(job_id);
'''


def setup_schema(db):
    db.executescript(SCHEMA)
    columns={r[1] for r in db.execute('PRAGMA table_info(users)')}
    if 'display_name' not in columns:db.execute("ALTER TABLE users ADD COLUMN display_name TEXT NOT NULL DEFAULT ''")
    if 'disabled' not in columns:db.execute('ALTER TABLE users ADD COLUMN disabled INTEGER NOT NULL DEFAULT 0 CHECK(disabled IN (0,1))')


def migrate_members(db):
    if db.execute("SELECT 1 FROM registry_meta WHERE key='memberships_initialized'").fetchone():return
    stamp=datetime.now(timezone.utc).isoformat(timespec='seconds')
    for row in db.execute('SELECT id,business_id FROM users').fetchall():
        db.execute('INSERT INTO memberships(id,business_id,user_id,role,created_at) VALUES(?,?,?,?,?)',(secrets.token_hex(16),row[1],row[0],'owner',stamp))
        db.execute("UPDATE users SET display_name='Local owner' WHERE id=? AND display_name=''",(row[0],))
    db.execute("INSERT INTO registry_meta VALUES('memberships_initialized','1')")