Database Schema

create table traceability.entities (
    -- Core columns
    entity_id uuid not null, -- Application ID
    mpob_id character varying null, -- Mandatory license to operate
    entity_name character varying null,
    address character varying null,
    latitude numeric(10, 8) null,
    longitude numeric(11, 8) null,    
    -- Additional info
    entity_code character varying null, -- SPOC code for smallholders
    mspo_id character varying null,
    uml_id character varying null, -- Rainforest Alliance
    rspo_type character varying null,
    mspo_type character varying null,
    entity_type character varying null,
    entity_group_name character varying null,
    parent_company character varying null,
    state character varying null,
    district character varying null,
    lot_number character varying null,
    hectares numeric(10, 2) null,
    data_source character varying null,
    -- Grouping
    is_master boolean null,
    master_entity_id uuid null,
    -- Tracking
    created_at timestamp with time zone null,
    updated_at timestamp with time zone null,
    created_by uuid null,
    updated_by uuid null,    
    verification_status character varying null,
    verified_by uuid null,
    verified_at timestamp with time zone null,
    constraint entities_pkey primary key (entity_id)
) TABLESPACE pg_default;

CREATE TABLE traceability.transactions (
  tx_id uuid NOT NULL DEFAULT gen_random_uuid (),
  -- From file upload
  seller_mpob_id VARCHAR(64) NOT NULL,
  seller_entity_type VARCHAR(50) NOT NULL,
  seller_entity_name VARCHAR(255) NOT NULL,
  quantity NUMERIC(12, 2) NOT NULL, -- Default to percentage, can add unit types later
  -- Application fields
  tx_start_date DATE NULL,
  tx_end_date DATE NULL, 
  buyer_mpob_id VARCHAR(64) NOT NULL, -- From logged-in user context
  upload_id UUID NULL, -- Identify which is the file upload
  -- Tracking
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()

  CONSTRAINT transactions_pkey PRIMARY KEY (tx_id)
  CONSTRAINT fk_upload FOREIGN KEY (upload_id)
    REFERENCES uploads(upload_id) ON DELETE CASCADE
);

create table traceability.uploads (
  upload_id UUID NOT NULL DEFAULT gen_random_uuid (),
  file_name VARCHAR(255) NOT NULL,
  file_size BIGINT NOT NULL,
  uploaded_by UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  CONSTRAINT uploads_pkey PRIMARY KEY (upload_id),
  CONSTRAINT fk_uploaded_by FOREIGN KEY (uploaded_by)
    REFERENCES auth.users(id)
);

create table traceability.user_entities (
  user_id UUID NOT NULL,
  mpob_id VARCHAR(64) NOT NULL,
  is_primary BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),  

  CONSTRAINT user_entities_pkey PRIMARY KEY (user_id, mpob_id),
  CONSTRAINT fk_user FOREIGN KEY (user_id)
    REFERENCES auth.users(id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TABLE traceability.user_profiles (
    user_id UUID PRIMARY KEY,
    role VARCHAR(20) DEFAULT 'viewer',
    updated_at TIMESTAMPTZ DEFAULT NOW(),  

    CONSTRAINT fk_user FOREIGN KEY (user_id)
      REFERENCES auth.users(id) ON DELETE CASCADE,
    CONSTRAINT check_role CHECK (
      role IN ('superadmin', 'admin', 'uploader', 'viewer')
    )
);

CREATE TABLE traceability.invitations (
    invitation_id UUID NOT NULL DEFAULT gen_random_uuid(),

    -- Core invitation data
    mpob_id VARCHAR(64) NOT NULL,  -- FFB dealer's MPOB ID

    -- Who's inviting (the mill)
    inviting_entity_id UUID NOT NULL,  -- Mill's entity_id
    invited_by UUID NOT NULL,  -- User at the mill
    invitation_token VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NULL, -- Dealer's email address

    -- Status tracking
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    invited_at TIMESTAMPTZ DEFAULT NOW(),

    -- User creation/linking reference
    user_id UUID NULL,  -- Links to auth.users after acceptance

    -- Enforce unique row identifier
    CONSTRAINT invitations_pkey PRIMARY KEY (invitation_id),
    -- Inviter is both an authenticated user and has entity_id
    CONSTRAINT fk_invited_by FOREIGN KEY (invited_by)
      REFERENCES auth.users(id),
    CONSTRAINT fk_inviting_entity FOREIGN KEY (inviting_entity_id)
      REFERENCES traceability.entities(entity_id),
    -- Dealer that accepts the invitation and creates a new user account
    CONSTRAINT fk_user FOREIGN KEY (user_id)
      REFERENCES auth.users(id),
    -- pending = waiting on dealer response
    -- accepted = dealer registered
    -- closed = dealer already registered. cancelled / superseded
    CONSTRAINT check_status CHECK (
      status IN ('pending', 'accepted', 'closed')
    ),
);

-- Allow multiple pending invitations per dealer, but only one per inviting entity
CREATE UNIQUE INDEX invitations_unique_pending_mill_dealer_idx
  ON traceability.invitations (mpob_id, inviting_entity_id)
  WHERE status = 'pending';

-- Enforce that only one invitation can ever be accepted for a dealer
CREATE UNIQUE INDEX invitations_unique_claimed_dealer_idx
  ON traceability.invitations (mpob_id)
  WHERE user_id IS NOT NULL;