sqlc_example/sqlc/schema.sql

-- vim:fileencoding=utf-8:foldmethod=marker

-- Extensions {{{
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_idkit";
-- }}}

-- Types {{{
CREATE TYPE weight AS ENUM ('mg', 'g', 'kg', 'lb', 'oz');
CREATE TYPE currency AS ENUM ('RUB', 'EUR', 'USD');
CREATE TYPE recipe_difficulty AS ENUM ('beginner', 'easy', 'medium', 'hard', 'expert');
CREATE DOMAIN color_hex AS VARCHAR(9)
CHECK (VALUE ~ '^#([0-9a-fA-F]{6}|[0-9a-fA-F]{3})$');
-- }}}

-- Tables {{{

    -- accounts {{{
    CREATE TABLE IF NOT EXISTS accounts (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        username VARCHAR(18) UNIQUE NOT NULL CHECK (LENGTH(username) BETWEEN 3 AND 18),

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_accounts AS SELECT * FROM accounts WHERE deleted_at IS NULL;
    CREATE INDEX idx_accounts_username ON accounts(username);
    -- }}}

    -- login_info {{{
    CREATE TABLE IF NOT EXISTS login_info (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),
        account_guid UUID UNIQUE NOT NULL REFERENCES accounts(guid),

        -- Email not necessary
        email VARCHAR(255) UNIQUE CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
        password_hash VARCHAR(60) NOT NULL,
        suspended_at TIMESTAMPTZ,
        suspended_reason VARCHAR(500),

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_login_info AS SELECT * FROM login_info WHERE deleted_at IS NULL;
    CREATE INDEX idx_login_info_account ON login_info(account_guid);
    -- }}}

    -- profiles {{{
    CREATE TABLE IF NOT EXISTS profiles (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),
        account_guid UUID UNIQUE NOT NULL REFERENCES accounts(guid),

        name VARCHAR(100) NOT NULL,
        surname VARCHAR(100),
        patronymic VARCHAR(100),

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_profiles AS SELECT * FROM profiles WHERE deleted_at IS NULL;
    CREATE INDEX idx_profiles_account ON profiles(account_guid);

    CREATE OR REPLACE VIEW view_account_profile AS
        SELECT
            a.guid,
            a.username,
            p.name,
            p.surname,
            p.patronymic,
            a.created_at,
            a.deleted_at
        FROM accounts a
        JOIN profiles p ON a.guid = p.account_guid
        WHERE a.deleted_at IS NULL;
    -- }}}

    -- dish_categories {{{
    CREATE TABLE IF NOT EXISTS dish_categories (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        title VARCHAR(30) UNIQUE NOT NULL,
        description VARCHAR(100),
        color color_hex,
        sort_order SMALLINT DEFAULT 0,
        thumbnail_s3_key UUID,

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_dish_categories AS SELECT * FROM dish_categories WHERE deleted_at IS NULL;
    CREATE INDEX idx_dish_categories_title ON dish_categories(title);
    -- }}}

    -- dishes {{{
    CREATE TABLE IF NOT EXISTS dishes (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        title VARCHAR(50) UNIQUE NOT NULL,
        description VARCHAR(500),
        instructions TEXT,
        preparation_time_minutes SMALLINT CHECK (preparation_time_minutes > 0),
        cooking_time_minutes SMALLINT CHECK (cooking_time_minutes >= 0),
        difficulty RECIPE_DIFFICULTY NOT NULL DEFAULT 'medium',
        thumbnail_s3_key UUID,

        made_public_at TIMESTAMPTZ,
        tags VARCHAR(20)[],

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_dishes AS SELECT * FROM dishes WHERE deleted_at IS NULL;
    CREATE INDEX idx_dishes_title ON dishes(title);
    CREATE INDEX idx_dishes_title_description_fts ON dishes 
        USING gin(to_tsvector('english', title || ' ' || description));
    CREATE INDEX idx_dishes_tags ON dishes USING gin(tags);
    -- }}}

    -- ingredients {{{
    CREATE TABLE IF NOT EXISTS ingredients (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        title VARCHAR(50) UNIQUE NOT NULL,
        description VARCHAR(500),
        thumbnail_s3_key UUID,

        made_public_at TIMESTAMPTZ,
        tags VARCHAR(20)[],

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_ingredients AS SELECT * FROM ingredients WHERE deleted_at IS NULL;
    CREATE INDEX idx_ingredients_title ON ingredients(title);
    CREATE INDEX idx_ingredients_title_description_fts ON dishes 
        USING gin(to_tsvector('english', title || ' ' || description));
    CREATE INDEX idx_ingredients_tags ON ingredients USING gin(tags);
    -- }}}

    -- stores {{{
    CREATE TABLE IF NOT EXISTS stores (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        title VARCHAR(50) UNIQUE NOT NULL,
        description VARCHAR(500),
        thumbnail_s3_key UUID,

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_stores AS SELECT * FROM stores WHERE deleted_at IS NULL;
    CREATE INDEX idx_stores_title ON stores(title);
    -- }}}

    -- ingredient_prices {{{
    CREATE TABLE IF NOT EXISTS ingredient_prices (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        ingredient_guid UUID NOT NULL REFERENCES ingredients(guid) ON DELETE CASCADE,
        price DECIMAL(10,2) NOT NULL CHECK (price > 0),
        price_currency CURRENCY NOT NULL DEFAULT 'USD',
        weight DECIMAL(8,3) NOT NULL CHECK (weight > 0),
        weight_unit WEIGHT NOT NULL DEFAULT 'g',
        store_guid UUID NOT NULL REFERENCES stores(guid) ON DELETE CASCADE,

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_ingredient_prices AS SELECT * FROM ingredient_prices WHERE deleted_at IS NULL;
    CREATE INDEX idx_ingredient_prices_ingredient_store ON ingredient_prices(ingredient_guid, store_guid);
    -- }}}

    -- dish_categories_dishes {{{
    CREATE TABLE IF NOT EXISTS dish_categories_dishes (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        dish_category_guid UUID NOT NULL REFERENCES dish_categories(guid) ON DELETE CASCADE,
        dish_guid UUID NOT NULL REFERENCES dishes(guid) ON DELETE CASCADE,
        UNIQUE(dish_category_guid, dish_guid),

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_dish_category_dishes AS SELECT * FROM dish_categories_dishes WHERE deleted_at IS NULL;
    --}}}

    -- dish_ingredients {{{
    CREATE TABLE IF NOT EXISTS dish_ingredients (
        guid UUID PRIMARY KEY DEFAULT idkit_uuidv7_generate_uuid(),

        dish_guid UUID NOT NULL REFERENCES dishes(guid) ON DELETE CASCADE,
        ingredient_guid UUID NOT NULL REFERENCES ingredients(guid) ON DELETE CASCADE,
        UNIQUE(dish_guid, ingredient_guid),
        
        amount DECIMAL(4,1) DEFAULT 1,
        weight DECIMAL(8,3),
        weight_unit WEIGHT NOT NULL DEFAULT 'g',
        CHECK (amount > 0 OR weight > 0),
        notes VARCHAR(200),

        author UUID REFERENCES accounts(guid) ON DELETE SET NULL,

        created_at TIMESTAMPTZ NOT NULL,
        updated_at TIMESTAMPTZ NOT NULL,
        deleted_at TIMESTAMPTZ
    );
    CREATE OR REPLACE VIEW active_dish_ingredients AS SELECT * FROM dish_ingredients WHERE deleted_at IS NULL;
    CREATE INDEX idx_dish_ingredients_dish_ingredient ON dish_ingredients(dish_guid, ingredient_guid);
    -- }}}
-- }}}

-- Triggers {{{

CREATE OR REPLACE FUNCTION handle_journal_insert()
RETURNS TRIGGER AS $$
BEGIN
   NEW.created_at = now();
   NEW.updated_at = now();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION handle_journal_update()
RETURNS TRIGGER AS $$
BEGIN
   NEW.created_at = OLD.created_at;
   NEW.updated_at = now();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$  
DECLARE
   tbl TEXT;
BEGIN
   FOREACH tbl IN ARRAY ARRAY[
        -- All tables must be listed here
        'accounts',
        'login_info',
        'profiles',
        'dishes',
        'dish_categories',
        'ingredients',
        'stores',
        'ingredient_prices',
        'dish_ingredients',
        'dish_categories_dishes'
    ]
   LOOP
      -- This creates views for all tables, which was supposed to be a relief.
      -- Unfortunately, SQLC does not see what we doo here; and while queries still work, structs
      -- are not created in Go code. Moreover, stars cannot be expanded. For this reason we manually
      -- declare boilerpla.. I mean views under each table. Otherwise this loop would be sufficient.
      -- Anyways, it does not hurt to have this
      -- EXECUTE format('
      --    CREATE OR REPLACE VIEW active_%s AS SELECT * FROM %s WHERE deleted_at IS NULL;
      --   ', tbl, tbl);

      -- Triggers for journal management
      EXECUTE format('
         CREATE OR REPLACE TRIGGER journal_insert_%s
         BEFORE INSERT ON %s
         FOR EACH ROW
         EXECUTE FUNCTION handle_journal_insert();
      ', tbl, tbl);
      EXECUTE format('
         CREATE OR REPLACE TRIGGER journal_update_%s
         BEFORE UPDATE ON %s
         FOR EACH ROW
         EXECUTE FUNCTION handle_journal_update();
      ', tbl, tbl);
   END LOOP;
END   $$;

-- }}}