-- =============================================================================
-- Stub Tables para Context Manager v1.0
-- Tablas temporales hasta integración con Sistema de Componentes TZZR
-- =============================================================================

-- PLY: Players/Usuarios
CREATE TABLE IF NOT EXISTS cto.ply (
    id BIGSERIAL PRIMARY KEY,
    hash CHAR(64) UNIQUE NOT NULL,
    name TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- FLG: Flags/Banderas
CREATE TABLE IF NOT EXISTS cto.flg (
    id BIGSERIAL PRIMARY KEY,
    hash CHAR(64) UNIQUE NOT NULL,
    name TEXT,
    value JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ITM: Items
CREATE TABLE IF NOT EXISTS cto.itm (
    id BIGSERIAL PRIMARY KEY,
    hash CHAR(64) UNIQUE NOT NULL,
    name TEXT,
    item_type TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- LOC: Locations
CREATE TABLE IF NOT EXISTS cto.loc (
    id BIGSERIAL PRIMARY KEY,
    hash CHAR(64) UNIQUE NOT NULL,
    name TEXT,
    coordinates JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HST: Hashtags
CREATE TABLE IF NOT EXISTS cto.hst (
    id BIGSERIAL PRIMARY KEY,
    hash CHAR(64) UNIQUE NOT NULL,
    tag TEXT UNIQUE,
    category TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Índices básicos
CREATE INDEX IF NOT EXISTS idx_ply_name ON cto.ply(name);
CREATE INDEX IF NOT EXISTS idx_flg_name ON cto.flg(name);
CREATE INDEX IF NOT EXISTS idx_itm_name ON cto.itm(name);
CREATE INDEX IF NOT EXISTS idx_itm_type ON cto.itm(item_type);
CREATE INDEX IF NOT EXISTS idx_loc_name ON cto.loc(name);
CREATE INDEX IF NOT EXISTS idx_hst_tag ON cto.hst(tag);
CREATE INDEX IF NOT EXISTS idx_hst_category ON cto.hst(category);