CREATE TABLE IF NOT EXISTS api_clients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_name VARCHAR(120) NOT NULL,
 api_key_hash CHAR(64) NOT NULL UNIQUE, allowed_ip VARCHAR(64) NULL,
 status ENUM('active','disabled') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS biometric_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, external_biometric_id BIGINT UNSIGNED NOT NULL UNIQUE,
 external_suspect_id BIGINT UNSIGNED NOT NULL, finger_position VARCHAR(50) NOT NULL,
 template_format VARCHAR(100) NOT NULL, template_version VARCHAR(30) NOT NULL,
 encrypted_template LONGTEXT NOT NULL, quality_score DECIMAL(7,3) NULL, scanner_model VARCHAR(180) NULL,
 status ENUM('active','disabled','revoked') NOT NULL DEFAULT 'active', enrolled_at DATETIME NOT NULL, updated_at DATETIME NULL,
 KEY idx_external_suspect(external_suspect_id), KEY idx_finger(finger_position), KEY idx_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS identification_searches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, search_reference VARCHAR(80) NOT NULL UNIQUE,
 quality_score DECIMAL(7,3) NULL, candidate_count INT NOT NULL DEFAULT 0,
 decision VARCHAR(30) NOT NULL, duration_ms INT NULL, created_at DATETIME NOT NULL,
 KEY idx_created(created_at), KEY idx_decision(decision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS afis_audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_type VARCHAR(80) NOT NULL,
 api_client_id BIGINT UNSIGNED NULL, request_reference VARCHAR(100) NULL, source_ip VARCHAR(64) NULL,
 details_json JSON NULL, created_at DATETIME NOT NULL, KEY idx_event(event_type), KEY idx_created(created_at),
 CONSTRAINT fk_audit_client FOREIGN KEY(api_client_id) REFERENCES api_clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS rate_limit_buckets (
 api_client_id BIGINT UNSIGNED NOT NULL, bucket_start DATETIME NOT NULL, request_count INT NOT NULL DEFAULT 0,
 PRIMARY KEY(api_client_id,bucket_start), CONSTRAINT fk_rate_client FOREIGN KEY(api_client_id) REFERENCES api_clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;