schema.sql

-- E7 Orbit maintained hero catalog.
-- Run this in Supabase SQL Editor before importing data.
-- The Android app only needs the publishable/anon key and read access.

create table if not exists public.hero_catalog (
    code text primary key,
    name text not null,
    rarity integer,
    attribute text not null default '',
    role text not null default '',
    zodiac text,
    description text,
    awakenings jsonb not null default '[]'::jsonb,
    memory_imprint jsonb not null default '{}'::jsonb,
    icon_url text,
    thumbnail_url text,
    image_url text,
    stats_attack integer,
    stats_health integer,
    stats_defense integer,
    base_attack integer,
    base_health integer,
    stats_speed integer,
    stats_critical_chance integer,
    stats_critical_damage integer,
    stats_effectiveness integer,
    stats_effect_resistance integer,
    stats_combat_power integer,
    source text not null default 'community-import',
    source_updated_at timestamptz,
    updated_at timestamptz not null default now()
);

create table if not exists public.hero_skills (
    hero_code text not null references public.hero_catalog(code) on delete cascade,
    slot integer not null check (slot between 1 and 5), -- 1-3 base, 4-5 transformed/extra skills (e.g. Tamarinne)
    name text not null default '',
    icon_url text,
    description text,
    enhanced_description text,
    cooldown integer,
    soul_gain integer,
    soul_requirement integer,
    soul_description text,
    attack_rate double precision,
    pow double precision,
    is_passive boolean not null default false,
    can_enhance boolean not null default false,
    values jsonb not null default '[]'::jsonb,
    enhancements jsonb not null default '[]'::jsonb,
    buff_slugs text[] not null default '{}',
    debuff_slugs text[] not null default '{}',
    source text not null default 'epic7db',
    source_updated_at timestamptz,
    updated_at timestamptz not null default now(),
    primary key (hero_code, slot)
);

create index if not exists hero_skills_hero_code_idx on public.hero_skills(hero_code);

create table if not exists public.hero_exclusive_equipment (
    code text primary key,
    hero_code text not null unique references public.hero_catalog(code) on delete cascade,
    name text not null check (length(trim(name)) > 0),
    description text,
    icon_url text not null check (length(trim(icon_url)) > 0),
    stat_type text not null check (stat_type in (
        'attack', 'health', 'defense', 'speed',
        'critical_chance', 'critical_damage',
        'effectiveness', 'effect_resistance'
    )),
    stat_min double precision not null,
    stat_max double precision not null check (stat_max >= stat_min),
    stat_percent boolean not null default false,
    enhancements jsonb not null check (
        case when jsonb_typeof(enhancements) = 'array'
            then jsonb_array_length(enhancements) = 3
                and enhancements @> '[{"option": 1}, {"option": 2}, {"option": 3}]'::jsonb
            else false
        end
    ),
    source text not null default 'gamekee',
    source_updated_at timestamptz,
    updated_at timestamptz not null default now(),
    check (code = 'ee-' || hero_code)
);

create index if not exists hero_exclusive_equipment_hero_code_idx
    on public.hero_exclusive_equipment(hero_code);

create table if not exists public.status_effect_catalog (
    slug text primary key,
    label text not null,
    description text,
    icon_url text,
    source text not null default 'gamedatabase',
    source_updated_at timestamptz,
    updated_at timestamptz not null default now()
);

create index if not exists hero_skills_buff_slugs_idx
    on public.hero_skills using gin (buff_slugs);
create index if not exists hero_skills_debuff_slugs_idx
    on public.hero_skills using gin (debuff_slugs);

alter table public.hero_catalog enable row level security;
alter table public.hero_skills enable row level security;
alter table public.hero_exclusive_equipment enable row level security;
alter table public.status_effect_catalog enable row level security;

drop policy if exists "Public can read hero catalog" on public.hero_catalog;
create policy "Public can read hero catalog"
    on public.hero_catalog for select
    to anon, authenticated
    using (true);

drop policy if exists "Public can read hero skills" on public.hero_skills;
create policy "Public can read hero skills"
    on public.hero_skills for select
    to anon, authenticated
    using (true);

drop policy if exists "Public can read hero exclusive equipment" on public.hero_exclusive_equipment;
create policy "Public can read hero exclusive equipment"
    on public.hero_exclusive_equipment for select
    to anon, authenticated
    using (true);

drop policy if exists "Public can read status effect catalog" on public.status_effect_catalog;
create policy "Public can read status effect catalog"
    on public.status_effect_catalog for select
    to anon, authenticated
    using (true);

create table if not exists public.artifact_catalog (
    code text primary key,
    name text not null,
    rarity integer,
    role text not null default '',
    description text,
    max_description text,
    lore text,
    image_url text,
    icon_url text,
    stats_attack integer,
    stats_health integer,
    stats_defense integer,
    base_attack integer,
    base_health integer,
    source text not null default 'epic7db',
    source_updated_at timestamptz,
    updated_at timestamptz not null default now()
);

alter table public.artifact_catalog enable row level security;

drop policy if exists "Public can read artifact catalog" on public.artifact_catalog;
create policy "Public can read artifact catalog"
    on public.artifact_catalog for select
    to anon, authenticated
    using (true);

-- The service_role key bypasses RLS for tools/sync-hero-catalog.mjs.
-- Do not create anonymous INSERT/UPDATE policies: mobile clients must be read-only.