-- Postgres schema + demo inventory for Pantry Sync.
-- Run in Supabase SQL editor or psql. Users for login are created via Supabase Auth
-- (signup or backend/scripts/seed-data.ts with service role key — not in this file).

BEGIN;

CREATE TABLE IF NOT EXISTS "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "email" varchar(255) NOT NULL,
  "full_name" varchar(255),
  "role" varchar(50) DEFAULT 'user' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "users_email_unique" UNIQUE("email")
);

CREATE TABLE IF NOT EXISTS "categories" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "name" varchar(100) NOT NULL,
  "description" text,
  "color" varchar(7),
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "categories_name_unique" UNIQUE("name")
);

CREATE TABLE IF NOT EXISTS "pantry_items" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "category_id" uuid,
  "name" varchar(255) NOT NULL,
  "description" text,
  "quantity" integer DEFAULT 0 NOT NULL,
  "unit" varchar(50),
  "expiry_date" timestamp,
  "purchase_date" timestamp DEFAULT now() NOT NULL,
  "price" numeric(10, 2),
  "location" varchar(100),
  "is_low_stock" boolean DEFAULT false,
  "low_stock_threshold" integer DEFAULT 5,
  "barcode" varchar(50),
  "image_url" text,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "orders" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "name" varchar(255) NOT NULL,
  "quantity" integer DEFAULT 1 NOT NULL,
  "unit" varchar(50),
  "is_purchased" boolean DEFAULT false,
  "priority" varchar(50) DEFAULT 'medium',
  "notes" text,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "order_items" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "order_id" uuid NOT NULL,
  "pantry_item_id" uuid,
  "name" varchar(255) NOT NULL,
  "quantity" integer DEFAULT 1 NOT NULL,
  "unit" varchar(50),
  "created_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "stock_analysis" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "analysis_type" varchar(100) NOT NULL,
  "data" json NOT NULL,
  "insights" text,
  "recommendations" text,
  "confidence" numeric(3, 2),
  "created_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "pantry_settings" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "pantry_name" varchar(255) DEFAULT 'Community Food Pantry' NOT NULL,
  "contact_phone" varchar(50),
  "address" text,
  "low_stock_threshold" integer DEFAULT 5 NOT NULL,
  "expiry_warning_days" integer DEFAULT 7 NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

-- drop first so re-run doesn't blow up
ALTER TABLE "pantry_items" DROP CONSTRAINT IF EXISTS "pantry_items_category_id_categories_id_fk";
ALTER TABLE "pantry_items" ADD CONSTRAINT "pantry_items_category_id_categories_id_fk"
  FOREIGN KEY ("category_id") REFERENCES "public"."categories"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "orders" DROP CONSTRAINT IF EXISTS "orders_user_id_users_id_fk";
ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "order_items" DROP CONSTRAINT IF EXISTS "order_items_order_id_orders_id_fk";
ALTER TABLE "order_items" ADD CONSTRAINT "order_items_order_id_orders_id_fk"
  FOREIGN KEY ("order_id") REFERENCES "public"."orders"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "order_items" DROP CONSTRAINT IF EXISTS "order_items_pantry_item_id_pantry_items_id_fk";
ALTER TABLE "order_items" ADD CONSTRAINT "order_items_pantry_item_id_pantry_items_id_fk"
  FOREIGN KEY ("pantry_item_id") REFERENCES "public"."pantry_items"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "stock_analysis" DROP CONSTRAINT IF EXISTS "stock_analysis_user_id_users_id_fk";
ALTER TABLE "stock_analysis" ADD CONSTRAINT "stock_analysis_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO "pantry_settings" ("id", "pantry_name", "contact_phone", "address", "low_stock_threshold", "expiry_warning_days")
VALUES (
  'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  'Campus Food Pantry (Demo)',
  '(555) 010-0000',
  '123 University Way, Demo City',
  5,
  7
)
ON CONFLICT ("id") DO NOTHING;

INSERT INTO "categories" ("id", "name", "description", "color") VALUES
  ('11111111-1111-1111-1111-111111111111', 'Produce', 'Fresh fruits and vegetables', '#4CAF50'),
  ('22222222-2222-2222-2222-222222222222', 'Canned Goods', 'Canned and preserved foods', '#FF9800'),
  ('33333333-3333-3333-3333-333333333333', 'Bakery', 'Bread and baked goods', '#8D6E63'),
  ('44444444-4444-4444-4444-444444444444', 'Grains', 'Rice, pasta, and grains', '#FDD835'),
  ('55555555-5555-5555-5555-555555555555', 'Dairy', 'Milk, cheese, and dairy products', '#2196F3')
ON CONFLICT ("name") DO NOTHING;

INSERT INTO "pantry_items" (
  "category_id", "name", "description", "quantity", "unit",
  "expiry_date", "price", "location", "is_low_stock", "low_stock_threshold"
) VALUES
  ('55555555-5555-5555-5555-555555555555', 'Milk (sample)', 'Gallon jugs', 20, 'gallons', now() + interval '7 days', 4.50, 'Fridge', false, 5),
  ('11111111-1111-1111-1111-111111111111', 'Apples (sample)', 'Bulk', 50, 'lbs', now() + interval '14 days', NULL, 'Dry storage', false, 10),
  ('22222222-2222-2222-2222-222222222222', 'Canned Beans (sample)', NULL, 100, 'cans', now() + interval '180 days', NULL, 'Shelves', false, 20),
  ('33333333-3333-3333-3333-333333333333', 'Whole Wheat Bread (sample)', NULL, 4, 'loaves', now() + interval '5 days', NULL, 'Bakery rack', true, 5);

-- Running twice duplicates pantry_items; truncate those if you need a clean rerun.

COMMIT;
