Database Management

-- INCISION Tenant Database Initial Schema
-- Version: 1
-- This script creates all required tables for a new tenant database

-- =====================================================
-- EXTENSIONS
-- =====================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================================================
-- ENUM TYPES
-- =====================================================

-- User role enum
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_role') THEN
        CREATE TYPE user_role AS ENUM ('admin', 'manager', 'viewer', 'vendor');
    END IF;
END $$;

-- Cost types
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'cost_type') THEN
        CREATE TYPE cost_type AS ENUM ('one_time', 'recurring', 'variable');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'cost_frequency') THEN
        CREATE TYPE cost_frequency AS ENUM ('one_time', 'monthly', 'quarterly', 'annually');
    END IF;
END $$;

-- RAID log enums
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'raid_category') THEN
        CREATE TYPE raid_category AS ENUM ('technical', 'operational', 'financial', 'legal', 'resource', 'external', 'strategic');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'risk_severity') THEN
        CREATE TYPE risk_severity AS ENUM ('critical', 'high', 'medium', 'low');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'risk_likelihood') THEN
        CREATE TYPE risk_likelihood AS ENUM ('very_high', 'high', 'medium', 'low', 'very_low');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'risk_impact') THEN
        CREATE TYPE risk_impact AS ENUM ('critical', 'high', 'medium', 'low', 'minimal');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'raid_status') THEN
        CREATE TYPE raid_status AS ENUM ('open', 'in_progress', 'mitigated', 'resolved', 'closed', 'accepted');
    END IF;
END $$;

-- Q&A enums
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'qa_question_status') THEN
        CREATE TYPE qa_question_status AS ENUM ('pending', 'assigned', 'draft_answer', 'answered', 'published', 'rejected', 'deferred');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'qa_question_category') THEN
        CREATE TYPE qa_question_category AS ENUM ('technical', 'commercial', 'legal', 'compliance', 'general');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'qa_question_priority') THEN
        CREATE TYPE qa_question_priority AS ENUM ('low', 'normal', 'high', 'urgent');
    END IF;
END $$;

-- =====================================================
-- CORE TABLES
-- =====================================================

-- Profiles table (extends auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  role user_role DEFAULT 'viewer',
  bio TEXT,
  permissions JSONB DEFAULT '{}',
  avatar_url TEXT,
  phone_number TEXT,
  department TEXT,
  job_title TEXT,
  is_approved BOOLEAN DEFAULT false,
  is_suspended BOOLEAN DEFAULT false,
  is_archived BOOLEAN DEFAULT false,
  must_change_password BOOLEAN DEFAULT false,
  approved_by UUID REFERENCES auth.users(id),
  approved_at TIMESTAMPTZ,
  theme_preference TEXT DEFAULT 'light',
  email_notifications JSONB DEFAULT '{"qa_new_question": true, "qa_answer_published": true, "qa_new_published": true}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Projects table
CREATE TABLE IF NOT EXISTS public.projects (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'on_hold', 'completed', 'archived')),
  start_date DATE,
  end_date DATE,
  owner_id UUID REFERENCES public.profiles(id),
  is_archived BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TSA Services table
CREATE TABLE IF NOT EXISTS public.tsa_services (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  service_id TEXT UNIQUE NOT NULL,
  service_name TEXT NOT NULL,
  description TEXT,
  service_owner TEXT,
  monthly_cost DECIMAL(12, 2) DEFAULT 0,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'transitioning', 'terminated', 'extended')),
  start_date DATE,
  end_date DATE,
  extension_date DATE,
  criticality TEXT DEFAULT 'medium' CHECK (criticality IN ('critical', 'high', 'medium', 'low')),
  exit_status TEXT DEFAULT 'not_started' CHECK (exit_status IN ('not_started', 'planning', 'in_progress', 'completed', 'delayed')),
  exit_progress INTEGER DEFAULT 0 CHECK (exit_progress >= 0 AND exit_progress <= 100),
  no

... (truncated)