PRAGMA journal_mode=WAL;
PRAGMA synchronous=FULL;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS leases (
    lease_id TEXT PRIMARY KEY,
    document_type TEXT NOT NULL,
    branch_code TEXT NOT NULL,
    terminal_code TEXT NOT NULL,
    environment TEXT NOT NULL,
    start_number INTEGER NOT NULL,
    end_number INTEGER NOT NULL,
    next_number INTEGER NOT NULL,
    expires_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    imported_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_leases_available
ON leases(document_type, environment, status, expires_at, next_number);

CREATE TABLE IF NOT EXISTS operations (
    id TEXT PRIMARY KEY,
    idempotency_key TEXT NOT NULL UNIQUE,
    external_reference TEXT,
    document_type TEXT NOT NULL,
    payload_ciphertext TEXT NOT NULL,
    payload_hash TEXT NOT NULL,
    state TEXT NOT NULL DEFAULT 'processing',
    lease_id TEXT,
    sequence_number INTEGER,
    consecutive TEXT,
    attempts INTEGER NOT NULL DEFAULT 0,
    last_error TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    synced_at TEXT,
    FOREIGN KEY (lease_id) REFERENCES leases(lease_id)
);

CREATE INDEX IF NOT EXISTS idx_operations_sync
ON operations(state, created_at);

CREATE TABLE IF NOT EXISTS sync_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    operation_id TEXT,
    event_type TEXT NOT NULL,
    detail_json TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY (operation_id) REFERENCES operations(id)
);