BEGIN;

ALTER TABLE device
    ADD COLUMN enrollment_mode text NOT NULL DEFAULT 'legacy' CHECK (enrollment_mode IN ('legacy','physical_confirm')),
    ADD COLUMN factory_device_id varchar(32) UNIQUE,
    ADD COLUMN factory_mac char(12) UNIQUE,
    ADD COLUMN binding_generation bigint NOT NULL DEFAULT 0 CHECK (binding_generation >= 0),
    ADD COLUMN onboarding_revision bigint NOT NULL DEFAULT 1 CHECK (onboarding_revision > 0),
    ADD COLUMN onboarding_released_at timestamptz,
    ADD COLUMN onboarding_boot_id uuid,
    ADD COLUMN onboarding_last_seen_at timestamptz;

ALTER TABLE device_binding ADD COLUMN generation bigint;
WITH history AS (
    SELECT id, 2 * row_number() OVER (PARTITION BY device_id ORDER BY bound_at,id) - 1 AS generation
    FROM device_binding
) UPDATE device_binding b SET generation=h.generation FROM history h WHERE b.id=h.id;
UPDATE device d SET binding_generation=COALESCE((SELECT max(b.generation + CASE WHEN b.unbound_at IS NULL THEN 0 ELSE 1 END) FROM device_binding b WHERE b.device_id=d.id),0);
CREATE UNIQUE INDEX device_binding_generation_key ON device_binding(device_id,generation);

CREATE TABLE device_onboarding_key (
    device_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    key_version bigint NOT NULL CHECK (key_version > 0),
    ciphertext bytea NOT NULL,
    status text NOT NULL CHECK (status IN ('PENDING','ACTIVE','REVOKED')),
    created_at timestamptz NOT NULL DEFAULT now(),
    activated_at timestamptz,
    PRIMARY KEY(device_id,key_version),
    FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id)
);
CREATE UNIQUE INDEX device_onboarding_one_active_key ON device_onboarding_key(device_id) WHERE status='ACTIVE';

CREATE TABLE device_enrollment_job (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    device_id uuid NOT NULL,
    actor_id uuid NOT NULL REFERENCES user_account(id),
    request_key varchar(160) NOT NULL,
    request_digest char(64) NOT NULL,
    batch_no varchar(120) NOT NULL,
    key_version bigint NOT NULL DEFAULT 1,
    status text NOT NULL DEFAULT 'PENDING' CHECK(status IN ('PENDING','DELIVERED','RELEASED','CANCELLED')),
    receiver_key text,
    delivery_ciphertext bytea,
    delivery_expires_at timestamptz,
    evidence_ref text,
    created_at timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id),
    UNIQUE(tenant_id,actor_id,request_key)
);

CREATE TABLE device_time_challenge (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id uuid NOT NULL REFERENCES device(id),
    key_version bigint NOT NULL,
    boot_id uuid NOT NULL,
    client_nonce varchar(32) NOT NULL,
    server_time_ms bigint NOT NULL,
    expires_at timestamptz NOT NULL,
    consumed_by uuid,
    UNIQUE(device_id,key_version,boot_id,client_nonce)
);

CREATE TABLE device_setup_session (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    boot_id uuid NOT NULL,
    local_setup_id uuid NOT NULL,
    binding_generation bigint NOT NULL,
    mode text NOT NULL CHECK(mode IN ('ENROLL','RECOVERY')),
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    closed_at timestamptz,
    close_reason text,
    web_ack_binding_id uuid,
    web_ack_generation bigint,
    web_ack_revision bigint,
    web_ack_at timestamptz,
    FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id)
);
CREATE INDEX device_setup_device_idx ON device_setup_session(device_id,created_at DESC);

CREATE TABLE device_handoff (
    token_hash char(64) PRIMARY KEY,
    setup_id uuid NOT NULL REFERENCES device_setup_session(id),
    code_challenge varchar(64) NOT NULL,
    expires_at timestamptz NOT NULL,
    consumed_at timestamptz
);

CREATE TABLE onboarding_web_session (
    token_hash char(64) PRIMARY KEY,
    setup_id uuid NOT NULL REFERENCES device_setup_session(id),
    csrf_hash char(64) NOT NULL,
    user_id uuid REFERENCES user_account(id),
    issuer text,
    subject text,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    revoked_at timestamptz
);

CREATE TABLE device_binding_claim (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    setup_id uuid NOT NULL REFERENCES device_setup_session(id),
    user_id uuid NOT NULL REFERENCES user_account(id),
    household_id uuid NOT NULL,
    subject_id uuid NOT NULL,
    expected_generation bigint NOT NULL,
    challenge_hash char(64) NOT NULL,
    challenge_ciphertext bytea NOT NULL,
    binding_digest char(64) NOT NULL,
    status text NOT NULL DEFAULT 'PENDING' CHECK(status IN ('PENDING','CONFIRMED','CANCELLED','EXPIRED')),
    expires_at timestamptz NOT NULL,
    press_event_id uuid,
    binding_id uuid REFERENCES device_binding(id),
    created_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id),
    FOREIGN KEY(household_id,tenant_id) REFERENCES household(id,tenant_id),
    FOREIGN KEY(subject_id,tenant_id) REFERENCES subject_profile(id,tenant_id),
    UNIQUE(device_id,press_event_id)
);
CREATE UNIQUE INDEX device_binding_claim_one_pending ON device_binding_claim(device_id) WHERE status='PENDING';

CREATE TABLE device_onboarding_request (
    actor_key text NOT NULL,
    scope text NOT NULL,
    request_id uuid NOT NULL,
    request_digest char(64) NOT NULL,
    device_id uuid NOT NULL REFERENCES device(id),
    binding_generation bigint NOT NULL,
    response_ciphertext bytea NOT NULL,
    expires_at timestamptz NOT NULL,
    PRIMARY KEY(actor_key,scope,request_id)
);

CREATE TABLE device_state_snapshot (
    device_id uuid NOT NULL REFERENCES device(id),
    revision bigint NOT NULL,
    binding_generation bigint NOT NULL,
    state_hash char(64) NOT NULL,
    state jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY(device_id,revision)
);
CREATE TABLE device_state_ack (
    device_id uuid PRIMARY KEY REFERENCES device(id),
    revision bigint NOT NULL,
    binding_generation bigint NOT NULL,
    state_hash char(64) NOT NULL,
    boot_id uuid NOT NULL,
    local_generation bigint NOT NULL CHECK(local_generation >= 0),
    accepted_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE device_activation_job (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id uuid NOT NULL UNIQUE,
    tenant_id uuid NOT NULL,
    provider_device_name varchar(128) NOT NULL UNIQUE,
    status text NOT NULL DEFAULT 'NOT_STARTED' CHECK(status IN ('NOT_STARTED','RESERVED','CONTACT_POSSIBLE','REGISTERED_OBSERVED','READY','RECONCILE_REQUIRED','BLOCKED')),
    binding_generation bigint NOT NULL,
    config_version bigint NOT NULL DEFAULT 1,
    config_ciphertext bytea,
    config_digest char(64),
    config_expires_at timestamptz,
    config_ack_at timestamptz,
    engine_ready_at timestamptz,
    current_attempt_id uuid,
    last_error_code text,
    reconciliation_evidence text,
    version bigint NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id)
);
CREATE TABLE device_activation_attempt (
    id uuid PRIMARY KEY,
    activation_id uuid NOT NULL REFERENCES device_activation_job(id),
    executor text NOT NULL CHECK(executor IN ('SERVER','DEVICE')),
    stage text NOT NULL CHECK(stage IN ('SERVER_REGISTER','ENGINE_INIT','CONVERSATION_TEST')),
    binding_generation bigint NOT NULL,
    config_version bigint NOT NULL,
    status text NOT NULL CHECK(status IN ('CONTACT_POSSIBLE','SUCCESS_OBSERVED','DEFINITE_FAILURE','UNKNOWN')),
    permit_expires_at timestamptz NOT NULL,
    permit_hash char(64),
    conversation_id uuid,
    sdk_result integer,
    evidence jsonb,
    result_digest char(64),
    created_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

CREATE TABLE onboarding_sms_challenge (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    session_hash char(64) NOT NULL REFERENCES onboarding_web_session(token_hash),
    phone_digest char(64) NOT NULL,
    phone_ciphertext bytea NOT NULL,
    code_hash char(64) NOT NULL,
    attempts integer NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'SENDING' CHECK(status IN ('SENDING','SENT','FAILED','CONSUMED')),
    request_id uuid NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    consumed_at timestamptz,
    UNIQUE(session_hash,request_id)
);
CREATE INDEX onboarding_sms_phone_time ON onboarding_sms_challenge(phone_digest,created_at DESC);

-- Every binding writer, including privacy deletion, advances the same generation.
-- Inserting ownership for a new-protocol device additionally needs a pending claim.
CREATE FUNCTION maintain_device_binding_generation() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE d device%ROWTYPE; claim_setting text; is_changed boolean;
BEGIN
    SELECT * INTO d FROM device WHERE id=COALESCE(NEW.device_id,OLD.device_id) FOR UPDATE;
    IF TG_OP='INSERT' THEN
        IF d.enrollment_mode='physical_confirm' THEN
            claim_setting := current_setting('app.onboarding_claim',true);
            IF NOT EXISTS(SELECT 1 FROM device_binding_claim c WHERE c.id::text=claim_setting
                AND c.device_id=NEW.device_id AND c.status='PENDING' AND c.expires_at>now()
                AND c.expected_generation=d.binding_generation AND c.user_id=NEW.bound_by
                AND c.household_id=NEW.household_id AND c.subject_id=NEW.subject_id) THEN
                RAISE EXCEPTION 'DEVICE_CONFIRMATION_REQUIRED' USING ERRCODE='P0001';
            END IF;
        END IF;
        NEW.generation := d.binding_generation+1;
        is_changed := true;
    ELSE
        IF NEW.device_id<>OLD.device_id OR NEW.household_id<>OLD.household_id OR NEW.subject_id<>OLD.subject_id OR NEW.generation IS DISTINCT FROM OLD.generation THEN
            RAISE EXCEPTION 'binding identity is immutable' USING ERRCODE='P0001';
        END IF;
        IF OLD.unbound_at IS NOT NULL AND NEW.unbound_at IS NULL THEN
            RAISE EXCEPTION 'closed binding cannot be restored' USING ERRCODE='P0001';
        END IF;
        is_changed := OLD.unbound_at IS NULL AND NEW.unbound_at IS NOT NULL;
    END IF;
    IF is_changed THEN
        UPDATE device SET binding_generation=binding_generation+1,onboarding_revision=onboarding_revision+1 WHERE id=d.id;
        UPDATE device_binding_claim SET status='CANCELLED',completed_at=now() WHERE device_id=d.id AND status='PENDING'
            AND id::text IS DISTINCT FROM current_setting('app.onboarding_claim',true);
        IF TG_OP='UPDATE' THEN
            UPDATE device_setup_session SET closed_at=COALESCE(closed_at,now()),close_reason='BINDING_CHANGED' WHERE device_id=d.id;
            UPDATE onboarding_web_session SET revoked_at=COALESCE(revoked_at,now()) WHERE setup_id IN (SELECT id FROM device_setup_session WHERE device_id=d.id);
            UPDATE device_activation_job SET config_ack_at=NULL,engine_ready_at=NULL,config_ciphertext=NULL,config_digest=NULL,
                status=CASE WHEN status IN ('CONTACT_POSSIBLE','RECONCILE_REQUIRED') THEN 'RECONCILE_REQUIRED' ELSE 'BLOCKED' END,
                last_error_code='BINDING_CHANGED',version=version+1,updated_at=now() WHERE device_id=d.id;
        END IF;
    END IF;
    RETURN NEW;
END $$;
CREATE TRIGGER device_binding_generation_guard BEFORE INSERT OR UPDATE ON device_binding FOR EACH ROW EXECUTE FUNCTION maintain_device_binding_generation();

COMMIT;