-- ═══════════════════════════════════════════════════════════════
-- SOMANTIX — Feature 1: Initial Database Schema
-- Migration: 001_initial_schema.sql
-- Date: April 17, 2026
-- 
-- Creates: 2 enums, 7 tables, indexes, triggers, helper functions
-- Run this in: Supabase Dashboard → SQL Editor
-- ═══════════════════════════════════════════════════════════════

-- ── STEP 1: CUSTOM ENUM TYPES ──

CREATE TYPE org_role_enum AS ENUM ('admin', 'member');
CREATE TYPE project_role_enum AS ENUM ('owner', 'contributor');


-- ── STEP 2: TABLES ──

-- 2.1 Organizations
CREATE TABLE organizations (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

-- 2.2 Users
CREATE TABLE users (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id     uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email               text NOT NULL UNIQUE,
  organization_role   org_role_enum NOT NULL DEFAULT 'member',
  is_superadmin       boolean NOT NULL DEFAULT false,
  display_name        text,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_organization_id ON users(organization_id);
CREATE INDEX idx_users_email ON users(email);

-- 2.3 Projects
CREATE TABLE projects (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name              text NOT NULL,
  client            text,
  status            text NOT NULL DEFAULT 'planning'
                    CHECK (status IN ('planning', 'in_production', 'archive')),
  policy            jsonb NOT NULL DEFAULT '{}'::jsonb,
  whitelist         jsonb NOT NULL DEFAULT '[]'::jsonb,
  blacklist         jsonb NOT NULL DEFAULT '[]'::jsonb,
  frameworks        jsonb NOT NULL DEFAULT '[]'::jsonb,
  project_attestations jsonb NOT NULL DEFAULT '[]'::jsonb,
  rights_holder     text,
  licensing_contact text,
  training_consent  text CHECK (training_consent IN ('opt_out', 'license_required', 'permitted', NULL)),
  policy_history    jsonb NOT NULL DEFAULT '[]'::jsonb,
  timeline          jsonb NOT NULL DEFAULT '[]'::jsonb,
  template_id       uuid,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_projects_owner_organization ON projects(owner_organization_id);
CREATE INDEX idx_projects_status ON projects(status);

-- 2.4 Project Assignments
CREATE TABLE project_assignments (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id        uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  organization_id   uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  role              project_role_enum NOT NULL,
  assigned_at       timestamptz NOT NULL DEFAULT now(),
  assigned_by       uuid REFERENCES users(id),

  UNIQUE (project_id, organization_id)
);

CREATE INDEX idx_pa_project ON project_assignments(project_id);
CREATE INDEX idx_pa_organization ON project_assignments(organization_id);
CREATE INDEX idx_pa_role ON project_assignments(role);

-- 2.5 Deliveries
CREATE TABLE deliveries (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id            uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  signer_id             uuid NOT NULL REFERENCES users(id),
  organization_id       uuid NOT NULL REFERENCES organizations(id),
  file_name             text NOT NULL,
  file_size             bigint,
  file_type             text,
  file_hash             text NOT NULL,
  signature             text,
  manifest              jsonb NOT NULL DEFAULT '{}'::jsonb,
  attestations          jsonb NOT NULL DEFAULT '{}'::jsonb,
  tools_used            jsonb NOT NULL DEFAULT '[]'::jsonb,
  scope                 jsonb,
  attestation_details   jsonb,
  training_consent      jsonb,
  status                text NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending', 'accepted', 'flagged', 'rejected')),
  disposition           jsonb,
  wrapped_at            timestamptz NOT NULL DEFAULT now(),
  reviewed_at           timestamptz,
  reviewed_by           uuid REFERENCES users(id),
  perceptual_hash       jsonb,
  created_at            timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_deliveries_project ON deliveries(project_id);
CREATE INDEX idx_deliveries_signer ON deliveries(signer_id);
CREATE INDEX idx_deliveries_organization ON deliveries(organization_id);
CREATE INDEX idx_deliveries_hash ON deliveries(file_hash);
CREATE INDEX idx_deliveries_status ON deliveries(status);
CREATE INDEX idx_deliveries_perceptual_hash ON deliveries
  USING gin (perceptual_hash jsonb_path_ops)
  WHERE perceptual_hash IS NOT NULL;

-- 2.6 Vendor Plans
CREATE TABLE vendor_plans (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id          uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  organization_id     uuid NOT NULL REFERENCES organizations(id),
  submitted_by        uuid NOT NULL REFERENCES users(id),
  plan_id             text NOT NULL UNIQUE,
  vendor              jsonb NOT NULL DEFAULT '{}'::jsonb,
  intended_use        jsonb NOT NULL DEFAULT '{}'::jsonb,
  intended_tools      jsonb NOT NULL DEFAULT '[]'::jsonb,
  frameworks          jsonb NOT NULL DEFAULT '[]'::jsonb,
  plan_data           jsonb NOT NULL DEFAULT '{}'::jsonb,
  tools_rationale     text,
  general_notes       text,
  status              text NOT NULL DEFAULT 'draft'
                      CHECK (status IN ('draft', 'submitted', 'accepted', 'declined', 'revision_requested')),
  revision            integer NOT NULL DEFAULT 1,
  imported_to_project boolean NOT NULL DEFAULT false,
  submitted_at        timestamptz,
  reviewed_at         timestamptz,
  reviewed_by         uuid REFERENCES users(id),
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_vp_project ON vendor_plans(project_id);
CREATE INDEX idx_vp_organization ON vendor_plans(organization_id);
CREATE INDEX idx_vp_plan_id ON vendor_plans(plan_id);
CREATE INDEX idx_vp_status ON vendor_plans(status);

-- 2.7 Templates
CREATE TABLE templates (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  created_by      uuid NOT NULL REFERENCES users(id),
  name        text NOT NULL,
  description text,
  policy      jsonb NOT NULL DEFAULT '{}'::jsonb,
  whitelist   jsonb NOT NULL DEFAULT '[]'::jsonb,
  blacklist   jsonb NOT NULL DEFAULT '[]'::jsonb,
  use_count   integer NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_templates_organization ON templates(organization_id);
CREATE INDEX idx_templates_created_by ON templates(created_by);

-- 2.8 Add FK from projects.template_id → templates.id
-- (deferred because templates table is created after projects)
ALTER TABLE projects
  ADD CONSTRAINT fk_projects_template
  FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE SET NULL;

CREATE INDEX idx_projects_template ON projects(template_id)
  WHERE template_id IS NOT NULL;


-- ── STEP 3: AUTO-UPDATE updated_at TRIGGER ──

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_organizations_updated_at
  BEFORE UPDATE ON organizations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_vendor_plans_updated_at
  BEFORE UPDATE ON vendor_plans
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_templates_updated_at
  BEFORE UPDATE ON templates
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();


-- ── STEP 4: AUTO-CREATE OWNER ASSIGNMENT ON PROJECT INSERT ──

CREATE OR REPLACE FUNCTION auto_create_owner_assignment()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO project_assignments (project_id, organization_id, role)
  VALUES (NEW.id, NEW.owner_organization_id, 'owner');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_project_auto_owner
  AFTER INSERT ON projects
  FOR EACH ROW EXECUTE FUNCTION auto_create_owner_assignment();


-- ═══════════════════════════════════════════════════════════════
-- VERIFICATION QUERIES (run these after the migration)
-- ═══════════════════════════════════════════════════════════════

-- Verify all tables exist
-- SELECT table_name FROM information_schema.tables
-- WHERE table_schema = 'public' ORDER BY table_name;
-- Expected: deliveries, organizations, project_assignments, projects, templates, users, vendor_plans

-- Verify RLS is enabled (should be auto-enabled)
-- SELECT tablename, rowsecurity FROM pg_tables
-- WHERE schemaname = 'public' ORDER BY tablename;
