create_schema.sql

-- This script creates the database schema for this sample application.
-- The script is executed by the DatabaseSeeder class.

CREATE TABLE IF NOT EXISTS singers (
    id         INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE) PRIMARY KEY,
    first_name STRING(MAX),
    last_name  STRING(MAX),
    full_name  STRING(MAX) AS (CASE WHEN first_name IS NULL THEN last_name
                                    WHEN last_name  IS NULL THEN first_name
                                    ELSE first_name || ' ' || last_name END) STORED,
    active     BOOL DEFAULT (TRUE),
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
);

CREATE TABLE IF NOT EXISTS albums (
    id               INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE) PRIMARY KEY,
    title            STRING(MAX) NOT NULL,
    marketing_budget NUMERIC,
    release_date     DATE,
    cover_picture    BYTES(MAX),
    singer_id        INT64 NOT NULL,
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT fk_albums_singers FOREIGN KEY (singer_id) REFERENCES singers (id)
);

CREATE TABLE IF NOT EXISTS tracks (
    id           INT64 NOT NULL,
    track_number INT64 NOT NULL,
    title        STRING(MAX) NOT NULL,
    sample_rate  FLOAT64 NOT NULL,
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
) PRIMARY KEY (id, track_number), INTERLEAVE IN PARENT albums ON DELETE CASCADE
;

CREATE TABLE IF NOT EXISTS venues (
    id          INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE) PRIMARY KEY,
    name        STRING(MAX) NOT NULL,
    description JSON NOT NULL,
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
);

CREATE TABLE IF NOT EXISTS concerts (
    id          INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE) PRIMARY KEY,
    venue_id    INT64 NOT NULL,
    singer_id   INT64 NOT NULL,
    name        STRING(MAX) NOT NULL,
    start_time  TIMESTAMP NOT NULL,
    end_time    TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    updated_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT fk_concerts_venues  FOREIGN KEY (venue_id)  REFERENCES venues  (id),
    CONSTRAINT fk_concerts_singers FOREIGN KEY (singer_id) REFERENCES singers (id),
    CONSTRAINT chk_end_time_after_start_time CHECK (end_time > start_time)
);