-- hasql-generate test schema
-- Run this against the target PostgreSQL database before compiling the test suite.

DROP SCHEMA IF EXISTS hg_test CASCADE;
CREATE SCHEMA hg_test;

-- Functional test table — CRUD and derived instances exercised at runtime.
CREATE TABLE hg_test.users (
    id      uuid        NOT NULL PRIMARY KEY DEFAULT uuidv7(),
    name    text        NOT NULL,
    email   text
);

-- Single UUID primary key, mix of NOT NULL and nullable columns.
CREATE TABLE hg_test.with_pk (
    pkid    uuid        NOT NULL PRIMARY KEY,
    name    text        NOT NULL,
    email   text,
    age     int4
);

-- No primary key — only decoder / encoder are generated.
CREATE TABLE hg_test.no_pk (
    label   text        NOT NULL,
    value   int8        NOT NULL,
    note    text
);

-- Composite primary key (uuid + int4).
CREATE TABLE hg_test.composite_pk (
    tenant_id   uuid    NOT NULL,
    item_id     int4    NOT NULL,
    payload     text    NOT NULL,
    PRIMARY KEY (tenant_id, item_id)
);

-- Enum type — used to test fromType generation.
CREATE TYPE hg_test.user_role AS ENUM ('admin', 'moderator', 'member');

-- Table referencing the enum type — tests that fromType's PgColumn instance
-- is picked up by a subsequent fromTable splice.
CREATE TABLE hg_test.with_role (
    id      uuid                NOT NULL PRIMARY KEY DEFAULT uuidv7(),
    name    text                NOT NULL,
    "role"  hg_test.user_role   NOT NULL
);

-- Single-column table for testing prefixed field name generation.
CREATE TABLE hg_test.prefix_demo (
    val     int4    NOT NULL
);

-- Single UUID PK with newtypePrimaryKeys — tests newtype PK wrapper generation.
CREATE TABLE hg_test.nt_single (
    id      uuid    NOT NULL PRIMARY KEY,
    name    text    NOT NULL,
    email   text
);

-- Composite PK with newtypePrimaryKeys — tests composite PK record wrapper generation.
CREATE TABLE hg_test.nt_composite (
    tenant_id   uuid    NOT NULL,
    item_id     int4    NOT NULL,
    payload     text    NOT NULL,
    PRIMARY KEY (tenant_id, item_id)
);

-- Reserved keyword columns — tests that generated identifiers get apostrophe suffix.
CREATE TABLE hg_test.keyword_cols (
    id      uuid    NOT NULL PRIMARY KEY DEFAULT uuidv7(),
    type    text    NOT NULL,
    class   text,
    data    int4    NOT NULL
);

-- View over users — read-only, used to test fromView generation.
CREATE VIEW hg_test.user_emails AS
    SELECT id, email FROM hg_test.users WHERE email IS NOT NULL;