-- ============================================================================
-- ALZAR FZE Website — PostgreSQL schema
-- Run once against a fresh database:
--   psql "$DATABASE_URL" -f database/schema.sql
-- Then seed an admin:
--   pnpm tsx scripts/create-admin.ts you@alzarfze.com 'a-strong-password' 'Your Name'
-- ============================================================================

-- Enum types
DO $$ BEGIN
  CREATE TYPE "role" AS ENUM ('user', 'admin');
EXCEPTION WHEN duplicate_object THEN null; END $$;

DO $$ BEGIN
  CREATE TYPE "submission_status" AS ENUM ('new', 'read', 'replied', 'archived');
EXCEPTION WHEN duplicate_object THEN null; END $$;

-- Users (admin auth)
CREATE TABLE IF NOT EXISTS "users" (
  "id"             serial PRIMARY KEY,
  "email"          varchar(320) NOT NULL UNIQUE,
  "password_hash"  text,
  "name"           text,
  "login_method"   varchar(64) DEFAULT 'password',
  "role"           "role" DEFAULT 'user' NOT NULL,
  "created_at"     timestamptz DEFAULT now() NOT NULL,
  "updated_at"     timestamptz DEFAULT now() NOT NULL,
  "last_signed_in" timestamptz DEFAULT now() NOT NULL
);

-- Contact form submissions
CREATE TABLE IF NOT EXISTS "contact_submissions" (
  "id"          serial PRIMARY KEY,
  "name"        varchar(255) NOT NULL,
  "email"       varchar(320) NOT NULL,
  "company"     varchar(255),
  "industry"    varchar(100),
  "message"     text NOT NULL,
  "status"      "submission_status" DEFAULT 'new' NOT NULL,
  "created_at"  timestamptz DEFAULT now() NOT NULL,
  "updated_at"  timestamptz DEFAULT now() NOT NULL
);

-- Helpful index for the admin dashboard (newest first, filter by status)
CREATE INDEX IF NOT EXISTS idx_submissions_status_created
  ON "contact_submissions" ("status", "created_at" DESC);

-- Auto-update updated_at on row changes (Postgres has no MySQL-style ON UPDATE)
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW."updated_at" = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_users_updated_at ON "users";
CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON "users"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

DROP TRIGGER IF EXISTS trg_submissions_updated_at ON "contact_submissions";
CREATE TRIGGER trg_submissions_updated_at
  BEFORE UPDATE ON "contact_submissions"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();