-- registry_schema.sql
CREATE DATABASE IF NOT EXISTS afwer_registry CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE afwer_registry;

CREATE TABLE IF NOT EXISTS afwer_staff (
  stftbl_id CHAR(32) NOT NULL PRIMARY KEY,
  stftbl_username VARCHAR(100),
  stftbl_email VARCHAR(255) NOT NULL UNIQUE,
  stftbl_password_hash VARCHAR(255) NOT NULL,
  stftbl_full_name VARCHAR(255),
  stftbl_role ENUM('superadmin','operator') NOT NULL DEFAULT 'operator',
  stftbl_is_active TINYINT(1) DEFAULT 1,
  stftbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_subscription_packages (
  subpkg_id CHAR(32) NOT NULL PRIMARY KEY,
  subpkg_name VARCHAR(150) NOT NULL,
  subpkg_price_cents BIGINT NOT NULL,
  subpkg_billing_cycle ENUM('monthly','yearly') NOT NULL,
  subpkg_features JSON NULL,
  subpkg_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_accounts (
  acntbl_id CHAR(32) NOT NULL PRIMARY KEY,
  acntbl_org_name VARCHAR(255),
  acntbl_account_slug VARCHAR(100) UNIQUE,
  acntbl_contact_email VARCHAR(255),
  acntbl_country_code CHAR(2),
  acntbl_region_code VARCHAR(10),
  acntbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  acntbl_status ENUM('active','suspended','deactivated') DEFAULT 'active',
  acntbl_tenant_db_name VARCHAR(100) UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_prefixes (
  preftbl_id CHAR(32) NOT NULL PRIMARY KEY,
  preftbl_account_id CHAR(32),
  preftbl_prefix VARCHAR(100) NOT NULL UNIQUE,
  preftbl_assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_resolver_index (
  restbl_id CHAR(32) NOT NULL PRIMARY KEY,
  restbl_doi VARCHAR(255) NOT NULL UNIQUE,
  restbl_account_id CHAR(32),
  restbl_tenant_db_name VARCHAR(100),
  restbl_object_type VARCHAR(50),
  restbl_object_id CHAR(32),
  restbl_target_url VARCHAR(1000),
  restbl_metadata JSON,
  restbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;