-- Création utilisateur + base
CREATE USER timechain_user WITH PASSWORD 'changeme';
CREATE DATABASE timechain2_core OWNER timechain_user;
\c timechain2_core

-- Table des blocs compressifs
CREATE TABLE blocks (
  block_id SERIAL PRIMARY KEY,
  parent_hash TEXT NOT NULL,
  block_hash TEXT NOT NULL UNIQUE,
  timestamp_utc TIMESTAMPTZ NOT NULL,
  vdf_tau_total INTEGER NOT NULL,
  vdf_tick_count INTEGER NOT NULL,
  signature TEXT NOT NULL
);

-- Table des événements horodatés
CREATE TABLE events (
  event_id TEXT PRIMARY KEY,
  block_id INTEGER REFERENCES blocks(block_id) ON DELETE SET NULL,
  payload JSONB NOT NULL,
  signature TEXT NOT NULL,
  author_pubkey TEXT NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL
);

-- Index pour les événements
CREATE INDEX idx_events_block_id ON events(block_id);
CREATE INDEX idx_events_timestamp ON events(timestamp);

-- Table des chaînes filles
CREATE TABLE child_chains (
  chain_id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  public_key TEXT NOT NULL UNIQUE,
  assigned_slot TEXT NOT NULL,
  authorized_by TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Table des révocations de chaînes filles
CREATE TABLE revocation_log (
  chain_id UUID,
  slot TEXT,
  reason TEXT,
  revoked_by TEXT,
  revoked_at TIMESTAMPTZ DEFAULT now()
);

-- Table de l'identité cryptographique du Core (clé publique active)
CREATE TABLE core_identity (
  pubkey TEXT PRIMARY KEY,
  signature TEXT,
  active BOOLEAN DEFAULT TRUE,
  registered_at TIMESTAMPTZ DEFAULT now()
);

-- Index sur blocs
CREATE INDEX idx_blocks_timestamp ON blocks(timestamp_utc);