supabase_schema.sql

-- =============================================================================
-- Fancy 2FA (Guardian Gate) - Full Database Schema
-- =============================================================================
-- Version: 1.0.2
-- Description: Complete database schema for fresh deployment (non-migration)
-- Usage: Run this SQL in your Supabase SQL Editor for new installations
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Users Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  client TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- -----------------------------------------------------------------------------
-- Tags Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tags (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  color TEXT NOT NULL
);

-- -----------------------------------------------------------------------------
-- Accounts Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS accounts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  issuer TEXT NOT NULL,
  account TEXT NOT NULL,
  secret TEXT NOT NULL,
  remark TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add comment for remark column
COMMENT ON COLUMN accounts.remark IS 'Account remark/note';

-- -----------------------------------------------------------------------------
-- Account_Tags Join Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS account_tags (
  account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE,
  tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (account_id, tag_id)
);

-- -----------------------------------------------------------------------------
-- Share Links Table
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS share_links (
  id SERIAL PRIMARY KEY,
  short_link VARCHAR(16) NOT NULL UNIQUE,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for share_links
CREATE INDEX IF NOT EXISTS idx_share_links_short_link ON share_links(short_link);
CREATE INDEX IF NOT EXISTS idx_share_links_account_id ON share_links(account_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_share_links_account_unique ON share_links(account_id);

-- -----------------------------------------------------------------------------
-- Row Level Security (RLS)
-- -----------------------------------------------------------------------------
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE account_tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE share_links ENABLE ROW LEVEL SECURITY;

-- -----------------------------------------------------------------------------
-- RLS Policies
-- -----------------------------------------------------------------------------
-- Note: These policies allow full access. For production, you may want to
-- implement more restrictive policies based on your authentication setup.
-- If using SERVICE_ROLE key in the backend, RLS is bypassed.

CREATE POLICY "Allow full access to service role" ON users FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow full access to service role" ON tags FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow full access to service role" ON accounts FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow full access to service role" ON account_tags FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow full access to service role" ON share_links FOR ALL USING (true) WITH CHECK (true);

-- -----------------------------------------------------------------------------
-- Default Data
-- -----------------------------------------------------------------------------
-- Insert default admin user if not exists
INSERT INTO users (name, email, client)
VALUES ('admin', 'admin@example.com', 'default')
ON CONFLICT DO NOTHING;