CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS payments (
  payment_id      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  stripe_id       TEXT NOT NULL UNIQUE,
  amount_pence    INTEGER NOT NULL,
  currency        TEXT NOT NULL DEFAULT 'gbp',
  status          TEXT NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','paid','failed','refunded')),
  paid_at         TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_payments_stripe_id ON payments(stripe_id);
CREATE INDEX IF NOT EXISTS idx_payments_status    ON payments(status);

CREATE TABLE IF NOT EXISTS customers (
  customer_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_id          TEXT NOT NULL UNIQUE,
  payment_id        UUID REFERENCES payments(payment_id),
  type              TEXT NOT NULL CHECK (type IN ('digital','physical')),
  sender_name       TEXT NOT NULL,
  sender_email      TEXT NOT NULL,
  recipient_name    TEXT NOT NULL,
  recipient_email   TEXT,
  address           JSONB,
  encrypted_image   BYTEA NOT NULL,
  encryption_iv     TEXT NOT NULL,
  encryption_tag    TEXT NOT NULL,
  decryption_key    TEXT NOT NULL,
  encrypted_message BYTEA NOT NULL,
  message_iv        TEXT NOT NULL,
  message_tag       TEXT NOT NULL,
  delivery_date     DATE NOT NULL,
  duration_days     INTEGER NOT NULL,
  tier_label        TEXT NOT NULL,
  delivered         BOOLEAN NOT NULL DEFAULT FALSE,
  delivered_at      TIMESTAMPTZ,
  posted            BOOLEAN NOT NULL DEFAULT FALSE,
  posted_at         TIMESTAMPTZ,
  print_job_id      TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_customers_delivery_date ON customers(delivery_date);
CREATE INDEX IF NOT EXISTS idx_customers_delivered     ON customers(delivered);
CREATE INDEX IF NOT EXISTS idx_customers_type          ON customers(type);
CREATE INDEX IF NOT EXISTS idx_customers_order_id      ON customers(order_id);

CREATE TABLE IF NOT EXISTS qr_codes (
  qr_code_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_id  UUID NOT NULL REFERENCES payments(payment_id),
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  url         TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_qr_codes_customer_id ON qr_codes(customer_id);
CREATE INDEX IF NOT EXISTS idx_qr_codes_payment_id  ON qr_codes(payment_id);
