-- WebMap Initial Schema
-- Creates websites and website_apps tables for multi-tenant domain routing

-- websites table: Domain to configuration mapping
CREATE TABLE IF NOT EXISTS websites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug VARCHAR(255) UNIQUE NOT NULL,
  domains TEXT[] NOT NULL,
  branding JSONB NOT NULL DEFAULT '{}',
  theme JSONB NOT NULL DEFAULT '{}',
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- GIN index for fast domain lookups via ANY()
CREATE INDEX IF NOT EXISTS idx_websites_domains ON websites USING GIN (domains);
CREATE INDEX IF NOT EXISTS idx_websites_slug ON websites (slug);
CREATE INDEX IF NOT EXISTS idx_websites_is_active ON websites (is_active) WHERE is_active = true;

-- website_apps table: App routing per website
CREATE TABLE IF NOT EXISTS website_apps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  website_id UUID NOT NULL REFERENCES websites(id) ON DELETE CASCADE,
  app VARCHAR(100) NOT NULL,
  base_path VARCHAR(255) NOT NULL DEFAULT '/',
  features JSONB NOT NULL DEFAULT '{}',
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(website_id, base_path)
);

CREATE INDEX IF NOT EXISTS idx_website_apps_website_id ON website_apps(website_id);
CREATE INDEX IF NOT EXISTS idx_website_apps_base_path ON website_apps(base_path);