-- =====================================================
-- 0. MASTER TABLE: m_synonym (HAS HEADER)
-- =====================================================
-- CSV columns: from, to
CREATE TABLE IF NOT EXISTS m_synonym (
  "from" TEXT PRIMARY KEY,
  "to" TEXT
);
-- =====================================================
-- SUPABASE DATABASE SETUP
-- =====================================================
-- Run these SQL commands in your Supabase SQL Editor
-- (Dashboard > SQL Editor > New Query)

-- =====================================================
-- 1. CREATE PROFILES TABLE
-- =====================================================
-- This table stores user profile information
-- It's linked to Supabase auth.users table

CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  email TEXT,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- =====================================================
-- 2. ENABLE ROW LEVEL SECURITY
-- =====================================================
-- This ensures users can only access their own data

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- =====================================================
-- 3. CREATE RLS POLICIES
-- =====================================================

-- Allow users to view their own profile
CREATE POLICY "Users can view their own profile"
  ON public.profiles
  FOR SELECT
  USING (auth.uid() = id);

-- Allow users to insert their own profile
CREATE POLICY "Users can insert their own profile"
  ON public.profiles
  FOR INSERT
  WITH CHECK (auth.uid() = id);

-- Allow users to update their own profile
CREATE POLICY "Users can update their own profile"
  ON public.profiles
  FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Allow users to delete their own profile (optional)
CREATE POLICY "Users can delete their own profile"
  ON public.profiles
  FOR DELETE
  USING (auth.uid() = id);

-- =====================================================
-- 4. CREATE FUNCTION TO HANDLE NEW USER SIGNUPS
-- =====================================================
-- Automatically creates a profile when a new user signs up

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- =====================================================
-- 5. CREATE TRIGGER FOR NEW USER SIGNUPS
-- =====================================================
-- Trigger the function whenever a new user is created

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

-- =====================================================
-- 6. CREATE FUNCTION TO UPDATE updated_at TIMESTAMP
-- =====================================================

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

-- =====================================================
-- 7. CREATE TRIGGER FOR updated_at
-- =====================================================

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_updated_at();

-- =====================================================
-- 8. EXAMPLE: CREATE A CUSTOM TABLE WITH RLS
-- =====================================================
-- Example of how to create your own tables with RLS

CREATE TABLE IF NOT EXISTS public.user_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE public.user_items ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their own items"
  ON public.user_items
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own items"
  ON public.user_items
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own items"
  ON public.user_items
  FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own items"
  ON public.user_items
  FOR DELETE
  USING (auth.uid() = user_id);

-- Add updated_at trigger
CREATE TRIGGER set_user_items_updated_at
  BEFORE UPDATE ON public.user_items
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_updated_at();

-- =====================================================
-- 9. OPTIONAL: CREATE STORAGE BUCKET
-- =====================================================
-- For file uploads (avatars, documents, etc.)
-- Run this in the SQL Editor or create via Dashboard > Storage

INSERT INTO storage.buckets (id, name, public)
VALUES ('avatars', 'avatars', true)
ON CONFLICT (id) DO NOTHING;

-- Create storage policy for avatars
CREATE POLICY "Avatar images are publicly accessible"
  ON storage.objects FOR SELECT
  USING (bucket_id = 'avatars');

CREATE POLICY "Users can upload their own avatar"
  ON storage.objects FOR INSERT
  WITH CHECK (
    bucket_id = 'avatars' AND
    auth.uid()::text = (storage.foldername(name))[1]
  );

CREATE POLICY "Users can update their own avatar"
  ON storage.objects FOR UPDATE
  USING (
    bucket_id = 'avatars' AND
    auth.uid()::text = (storage.foldername(name))[1]
  );

CREATE POLICY "Users can delete their own avatar"
  ON storage.objects FOR DELETE
  USING (
    bucket_id = 'avatars' AND
    auth.uid()::text = (storage.foldername(name))[1]
  );

-- =====================================================
-- 10. MASTER DATA AND FACT TABLES
-- =====================================================
-- SQL schema for master data and fact tables from CSV files
-- CSV Import Notes:
-- - Master tables (m_*) have headers: skip 1 row on import
-- - Fact tables have NO headers: import from row 1
-- - Keys with leading zeros are VARCHAR to preserve formatting
-- - Currency fields NUMERIC(18,2); volumes NUMERIC(18,3)


-- =====================================================
-- 1. MASTER DATA TABLES (HAS HEADER)
-- =====================================================

-- m_synonym
-- CSV columns: from, to
CREATE TABLE IF NOT EXISTS m_synonym (
  "from" TEXT PRIMARY KEY,
  "to" TEXT
);

-- m_country
-- CSV columns: Index,Country_code,Country,Region,Subregion,Country_key
CREATE TABLE IF NOT EXISTS m_country (
  idx INTEGER NULL,
  country_code VARCHAR(10) NOT NULL,
  country VARCHAR(100) NOT NULL,
  region VARCHAR(100) NULL,
  subregion VARCHAR(100) NULL,
  country_key VARCHAR(64) NOT NULL,
  CONSTRAINT pk_m_country PRIMARY KEY (country_key)
);
CREATE INDEX IF NOT EXISTS ix_m_country_code ON m_country(country_code);
CREATE INDEX IF NOT EXISTS ix_m_country_region ON m_country(region);

-- m_customer_shipto
-- CSV columns: Customer_shipto_key,Customer_shipto
CREATE TABLE IF NOT EXISTS m_customer_shipto (
  customer_shipto_key VARCHAR(10) NOT NULL,
  customer_shipto VARCHAR(255) NOT NULL,
  CONSTRAINT pk_m_customer_shipto PRIMARY KEY (customer_shipto_key)
);

-- m_customer_soldto
-- CSV columns: Customer_soldto_key,Customer_soldto,Customer_group
CREATE TABLE IF NOT EXISTS m_customer_soldto (
  customer_soldto_key VARCHAR(10) NOT NULL,
  customer_soldto VARCHAR(255) NOT NULL,
  customer_group VARCHAR(100) NULL,
  CONSTRAINT pk_m_customer_soldto PRIMARY KEY (customer_soldto_key)
);

-- m_product
-- CSV columns: Product_article_key,Product_article,Product_group_key,Product_group
CREATE TABLE IF NOT EXISTS m_product (
  product_article_key VARCHAR(10) NOT NULL,
  product_article VARCHAR(255) NOT NULL,
  product_group_key VARCHAR(10) NOT NULL,
  product_group VARCHAR(255) NOT NULL,
  CONSTRAINT pk_m_product PRIMARY KEY (product_article_key)
);
CREATE INDEX IF NOT EXISTS ix_m_product_group_key ON m_product(product_group_key);

-- Fact Table: fact_inventory.csv (NO HEADER)
-- CSV columns: Country_key, Product_article_key, Inventory_volume, Source
CREATE TABLE fact_inventory (
  country_key VARCHAR(64) NOT NULL,
  product_article_key VARCHAR(10) NOT NULL,
  inventory_volume NUMERIC(18,3) NOT NULL,
  source VARCHAR(32) NOT NULL,
  CONSTRAINT pk_fact_inventory PRIMARY KEY (country_key, product_article_key),
  CONSTRAINT fk_inv_country FOREIGN KEY (country_key) REFERENCES m_country(country_key),
  CONSTRAINT fk_inv_product FOREIGN KEY (product_article_key) REFERENCES m_product(product_article_key)
);

CREATE INDEX ix_inv_country ON fact_inventory(country_key);
CREATE INDEX ix_inv_product ON fact_inventory(product_article_key);

-- Fact Table: fact_orders.csv (NO HEADER)
-- CSV columns: Date, Country_key, Document_number, Customer_soldto_key, Customer_shipto_key,
--              Product_article_key, PO_number, Order_volume, Order_eur, Order_lc, Order_usd, Source
CREATE TABLE fact_orders (
  date DATE NOT NULL,
  country_key VARCHAR(64) NOT NULL,
  document_number VARCHAR(40) NOT NULL,
  customer_soldto_key VARCHAR(10) NOT NULL,
  customer_shipto_key VARCHAR(10) NOT NULL,
  product_article_key VARCHAR(10) NOT NULL,
  po_number VARCHAR(40) NULL,
  order_volume NUMERIC(18,3) NOT NULL,
  order_eur NUMERIC(18,2) NULL,
  order_lc NUMERIC(18,2) NULL,
  order_usd NUMERIC(18,2) NULL,
  source VARCHAR(32) NOT NULL,
  CONSTRAINT pk_fact_orders PRIMARY KEY (document_number),
  CONSTRAINT fk_ord_country FOREIGN KEY (country_key) REFERENCES m_country(country_key),
  CONSTRAINT fk_ord_soldto FOREIGN KEY (customer_soldto_key) REFERENCES m_customer_soldto(customer_soldto_key),
  CONSTRAINT fk_ord_shipto FOREIGN KEY (customer_shipto_key) REFERENCES m_customer_shipto(customer_shipto_key),
  CONSTRAINT fk_ord_product FOREIGN KEY (product_article_key) REFERENCES m_product(product_article_key)
);

CREATE INDEX ix_ord_date ON fact_orders(date);
CREATE INDEX ix_ord_country ON fact_orders(country_key);
CREATE INDEX ix_ord_soldto ON fact_orders(customer_soldto_key);
CREATE INDEX ix_ord_shipto ON fact_orders(customer_shipto_key);
CREATE INDEX ix_ord_product ON fact_orders(product_article_key);

-- Fact Table: fact_overdues.csv (NO HEADER)
-- CSV columns: Country_key, Customer_soldto_key, Customer_shipto_key, Product_article_key,
--              Document_number, Due_date, Overdue_eur, Overdue_lc, Overdue_usd, Source
CREATE TABLE fact_overdues (
  country_key VARCHAR(64) NOT NULL,
  customer_soldto_key VARCHAR(10) NOT NULL,
  customer_shipto_key VARCHAR(10) NOT NULL,
  product_article_key VARCHAR(10) NOT NULL,
  document_number VARCHAR(40) NOT NULL,
  due_date DATE NOT NULL,
  overdue_eur NUMERIC(18,2) NULL,
  overdue_lc NUMERIC(18,2) NULL,
  overdue_usd NUMERIC(18,2) NULL,
  source VARCHAR(32) NOT NULL,
  CONSTRAINT pk_fact_overdues PRIMARY KEY (document_number),
  CONSTRAINT fk_ovd_country FOREIGN KEY (country_key) REFERENCES m_country(country_key),
  CONSTRAINT fk_ovd_soldto FOREIGN KEY (customer_soldto_key) REFERENCES m_customer_soldto(customer_soldto_key),
  CONSTRAINT fk_ovd_shipto FOREIGN KEY (customer_shipto_key) REFERENCES m_customer_shipto(customer_shipto_key),
  CONSTRAINT fk_ovd_product FOREIGN KEY (product_article_key) REFERENCES m_product(product_article_key)
);

CREATE INDEX ix_ovd_due_date ON fact_overdues(due_date);
CREATE INDEX ix_ovd_country ON fact_overdues(country_key);
CREATE INDEX ix_ovd_soldto ON fact_overdues(customer_soldto_key);
CREATE INDEX ix_ovd_shipto ON fact_overdues(customer_shipto_key);
CREATE INDEX ix_ovd_product ON fact_overdues(product_article_key);

-- Fact Table: fact_sales.csv (NO HEADER)
-- CSV columns: Date, Country_key, Customer_soldto_key, Customer_shipto_key, Product_article_key,
--              Sales_volume, Sales_eur, Sales_lc, Sales_usd, Source
CREATE TABLE fact_sales (
  date DATE NOT NULL,
  country_key VARCHAR(64) NOT NULL,
  customer_soldto_key VARCHAR(10) NOT NULL,
  customer_shipto_key VARCHAR(10) NOT NULL,
  product_article_key VARCHAR(10) NOT NULL,
  sales_volume NUMERIC(18,3) NOT NULL,
  sales_eur NUMERIC(18,2) NULL,
  sales_lc NUMERIC(18,2) NULL,
  sales_usd NUMERIC(18,2) NULL,
  source VARCHAR(32) NOT NULL,
  CONSTRAINT pk_fact_sales PRIMARY KEY (date, country_key, customer_soldto_key, customer_shipto_key, product_article_key),
  CONSTRAINT fk_sal_country FOREIGN KEY (country_key) REFERENCES m_country(country_key),
  CONSTRAINT fk_sal_soldto FOREIGN KEY (customer_soldto_key) REFERENCES m_customer_soldto(customer_soldto_key),
  CONSTRAINT fk_sal_shipto FOREIGN KEY (customer_shipto_key) REFERENCES m_customer_shipto(customer_shipto_key),
  CONSTRAINT fk_sal_product FOREIGN KEY (product_article_key) REFERENCES m_product(product_article_key)
);

CREATE INDEX ix_sal_date ON fact_sales(date);
CREATE INDEX ix_sal_country ON fact_sales(country_key);
CREATE INDEX ix_sal_soldto ON fact_sales(customer_soldto_key);
CREATE INDEX ix_sal_shipto ON fact_sales(customer_shipto_key);
CREATE INDEX ix_sal_product ON fact_sales(product_article_key);

-- =====================================================
-- 11. ENABLE RLS FOR MASTER DATA TABLES
-- =====================================================
-- Enable Row Level Security for master data tables
-- These policies allow all authenticated users to access master data


-- =====================================================
-- 2. ENABLE RLS FOR MASTER DATA TABLES
-- =====================================================

-- Enable RLS on master tables
ALTER TABLE m_synonym ENABLE ROW LEVEL SECURITY;
ALTER TABLE m_country ENABLE ROW LEVEL SECURITY;
ALTER TABLE m_customer_shipto ENABLE ROW LEVEL SECURITY;
ALTER TABLE m_customer_soldto ENABLE ROW LEVEL SECURITY;
ALTER TABLE m_product ENABLE ROW LEVEL SECURITY;

-- RLS Policies for m_synonym
CREATE POLICY "Authenticated users can view synonyms"
  ON m_synonym FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Authenticated users can insert synonyms"
  ON m_synonym FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update synonyms"
  ON m_synonym FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can delete synonyms"
  ON m_synonym FOR DELETE
  TO authenticated
  USING (true);

-- RLS Policies for m_country
CREATE POLICY "Authenticated users can view countries"
  ON m_country FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Authenticated users can insert countries"
  ON m_country FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update countries"
  ON m_country FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can delete countries"
  ON m_country FOR DELETE
  TO authenticated
  USING (true);

-- RLS Policies for m_customer_shipto
CREATE POLICY "Authenticated users can view ship-to customers"
  ON m_customer_shipto FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Authenticated users can insert ship-to customers"
  ON m_customer_shipto FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update ship-to customers"
  ON m_customer_shipto FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can delete ship-to customers"
  ON m_customer_shipto FOR DELETE
  TO authenticated
  USING (true);

-- RLS Policies for m_customer_soldto
CREATE POLICY "Authenticated users can view sold-to customers"
  ON m_customer_soldto FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Authenticated users can insert sold-to customers"
  ON m_customer_soldto FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update sold-to customers"
  ON m_customer_soldto FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can delete sold-to customers"
  ON m_customer_soldto FOR DELETE
  TO authenticated
  USING (true);

-- RLS Policies for m_product
CREATE POLICY "Authenticated users can view products"
  ON m_product FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Authenticated users can insert products"
  ON m_product FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update products"
  ON m_product FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can delete products"
  ON m_product FOR DELETE
  TO authenticated
  USING (true);

-- =====================================================
-- DONE! 
-- =====================================================
-- Your Supabase database is now set up with:
-- ✅ Profiles table with RLS
-- ✅ Automatic profile creation on signup
-- ✅ Example custom table (user_items)
-- ✅ Storage bucket for avatars
-- ✅ Master data tables with RLS policies
--
-- Next steps:
-- 1. Update your .env file with Supabase credentials
-- 2. Configure OAuth providers in Supabase Dashboard (optional)
-- 3. Test authentication from your frontend
-- 4. Import CSV data into master and fact tables

-- Table for storing embeddings and metadata for filter values and RAG vectors. This is a unified table that can be used for both purposes, differentiated by the 'type' column.
CREATE TABLE IF NOT EXISTS public.rag_vector (
  id uuid not null default gen_random_uuid (),
  embeddings public.vector not null,
  value_to_be_embedded character varying(200) not null,
  actual_value character varying(255) null,
  column_name character varying(255) null,
  type character varying(200) not null,
  created_at timestamp with time zone null default now(),
  updated_at timestamp with time zone null default now(),
  constraint rag_vector_pkey primary key (id)
) TABLESPACE pg_default;

create index IF not exists ix_rag_vector_type on public.rag_vector using btree (type) TABLESPACE pg_default;
create index IF not exists ix_rag_vector_value on public.rag_vector using btree (value_to_be_embedded) TABLESPACE pg_default;

-- RLS Policies for profiles_rls
create table public.profiles_rls (
  id bigint generated by default as identity not null,
  email text not null,
  source text not null,
  country text not null,
  customer_soldto text not null,
  constraint profiles_rls_pkey primary key (id)
) TABLESPACE pg_default;


-- Supabase setup script for documents storage and metadata
-- 1) Create a Storage bucket named 'documents' via Supabase Dashboard -> Storage
--    (Set Public Access = false if you want private files.)

-- 2) Optional: ensure pgcrypto extension (for gen_random_uuid)
-- Run in SQL editor if your DB allows extensions
create extension if not exists pgcrypto;

-- 3) Create metadata table
create table if not exists documents (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) not null,
  filename text not null,
  storage_path text not null,
  uploaded_at timestamp with time zone default now()
);

-- 4) Enable Row-Level Security (RLS)
alter table documents enable row level security;

-- 5) Policies
-- Users can only view their own documents
drop policy if exists "Users can view own documents" on documents;
create policy "Users can view own documents"
on documents for select
using (auth.uid() = user_id);

-- Users can only insert documents with their own user_id
drop policy if exists "Users can insert own documents" on documents;
create policy "Users can insert own documents"
on documents for insert
with check (auth.uid() = user_id);

-- Users can update only their own documents
drop policy if exists "Users can update own documents" on documents;
create policy "Users can update own documents"
on documents for update
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

-- Users can delete only their own documents
drop policy if exists "Users can delete own documents" on documents;
create policy "Users can delete own documents"
on documents for delete
using (auth.uid() = user_id);

-- Notes:
-- - Create the storage bucket in the Supabase dashboard called `documents`.
-- - Make sure your service_role key or server-side client is used for storage uploads
--   from a trusted backend (do not embed the service_role key in frontend code).


create table public.rag_user_documents (
  id uuid not null default gen_random_uuid (),
  user_id uuid not null,
  content text not null,
  embedding double precision[] not null,
  doc_id uuid null,
  constraint rag_user_documents_pkey primary key (id),
  constraint rag_user_documents_doc_id_fkey foreign KEY (doc_id) references documents (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;