"""Local SQLite approval fixture. No email, external effects or real authentication.
Reviewer/tenant identity supplied by tests represents trusted application context.
Production identity, destination idempotency and cross-system recovery are absent.
"""
import hashlib
import json
import sqlite3
import tempfile
import unittest
from pathlib import Path


def fingerprint(payload):
    return hashlib.sha256(json.dumps(payload, sort_keys=True, separators=(',', ':')).encode()).hexdigest()


class Store:
    def __init__(self, path):
        self.db = sqlite3.connect(path, isolation_level=None)
        self.db.execute('CREATE TABLE IF NOT EXISTS actions(id TEXT PRIMARY KEY, tenant TEXT, digest TEXT, expires INTEGER, state TEXT)')
        self.db.execute('CREATE TABLE IF NOT EXISTS effects(action_id TEXT PRIMARY KEY, payload TEXT)')

    def create(self, action_id, tenant, payload, expires):
        self.db.execute('INSERT INTO actions VALUES(?,?,?,?,?)', (action_id, tenant, fingerprint(payload), expires, 'pending'))

    def decide(self, action_id, tenant, actor, approve):
        if actor != 'reviewer-1':
            raise PermissionError('Reviewer not authorized in this fixture')
        changed = self.db.execute('UPDATE actions SET state=? WHERE id=? AND tenant=? AND state=?', ('approved' if approve else 'rejected', action_id, tenant, 'pending')).rowcount
        if changed != 1:
            raise PermissionError('Action is not pending in this tenant')

    def execute(self, action_id, tenant, payload, now):
        self.db.execute('BEGIN IMMEDIATE')
        try:
            row = self.db.execute('SELECT tenant,digest,expires,state FROM actions WHERE id=?', (action_id,)).fetchone()
            if not row or row[0] != tenant or row[1] != fingerprint(payload):
                raise PermissionError('Action identity or payload mismatch')
            if row[3] == 'succeeded':
                result = 'already-completed'
            elif row[3] != 'approved' or now >= row[2]:
                raise PermissionError('Not approved or approval expired')
            else:
                # This is an atomic LOCAL effect stub, never an external send.
                self.db.execute('INSERT INTO effects VALUES(?,?)', (action_id, json.dumps(payload)))
                self.db.execute('UPDATE actions SET state=? WHERE id=?', ('succeeded', action_id))
                result = 'completed'
            self.db.execute('COMMIT')
            return result
        except Exception:
            self.db.execute('ROLLBACK')
            raise

    def count(self):
        return self.db.execute('SELECT COUNT(*) FROM effects').fetchone()[0]

    def close(self):
        self.db.close()


class ApprovalChecks(unittest.TestCase):
    def setUp(self):
        self.tmp = tempfile.TemporaryDirectory()
        self.path = Path(self.tmp.name) / 'fixture.sqlite3'
        self.store = Store(self.path)
        self.payload = {'to': 'customer@example.invalid', 'body': 'Exact reviewed text', 'attachment': 'example-digest'}
        self.store.create('a1', 'team-a', self.payload, expires=100)

    def tearDown(self):
        self.store.close()
        self.tmp.cleanup()

    def approve(self):
        self.store.decide('a1', 'team-a', 'reviewer-1', True)

    def test_durable_pause(self):
        self.approve()
        self.store.close()
        self.store = Store(self.path)
        self.assertEqual(self.store.execute('a1', 'team-a', self.payload, 10), 'completed')
        self.assertEqual(self.store.count(), 1)

    def test_duplicate_resume_after_reopen(self):
        self.approve()
        self.store.execute('a1', 'team-a', self.payload, 10)
        self.store.close()
        self.store = Store(self.path)
        self.assertEqual(self.store.execute('a1', 'team-a', self.payload, 11), 'already-completed')
        self.assertEqual(self.store.count(), 1)

    def test_reject(self):
        self.store.decide('a1', 'team-a', 'reviewer-1', False)
        with self.assertRaises(PermissionError):
            self.store.execute('a1', 'team-a', self.payload, 10)
        self.assertEqual(self.store.count(), 0)

    def test_expiry_boundary(self):
        self.approve()
        with self.assertRaises(PermissionError):
            self.store.execute('a1', 'team-a', self.payload, 100)
        self.assertEqual(self.store.count(), 0)

    def test_changed_payload(self):
        self.approve()
        with self.assertRaises(PermissionError):
            self.store.execute('a1', 'team-a', {**self.payload, 'to': 'other@example.invalid'}, 10)
        self.assertEqual(self.store.count(), 0)

    def test_wrong_reviewer(self):
        with self.assertRaises(PermissionError):
            self.store.decide('a1', 'team-a', 'outsider', True)
        self.assertEqual(self.store.count(), 0)

    def test_wrong_tenant(self):
        self.approve()
        with self.assertRaises(PermissionError):
            self.store.execute('a1', 'team-b', self.payload, 10)
        self.assertEqual(self.store.count(), 0)


if __name__ == '__main__':
    unittest.main(verbosity=2)