--up
CREATE TYPE category AS ENUM (
    'Edit',
    'Effect',
    'Generator'
);

CREATE TYPE user_environment AS ENUM (
    'production',
    'staging',
    'beta'
);

CREATE TYPE user_role AS ENUM (
    'waitlist',
    'standard',
    'admin'
);

CREATE TABLE users (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    normalized_email text NOT NULL UNIQUE,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL,
    role user_role DEFAULT 'standard'::user_role NOT NULL,
    stripe_customer_id text,
    message_limit_override integer,
    environment user_environment DEFAULT 'production'::user_environment NOT NULL
);
CREATE INDEX users_stripe_customer_id_idx ON users USING btree (stripe_customer_id);

CREATE TABLE activity (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    action text NOT NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    ip text
);
CREATE INDEX activity_user_id_idx ON activity USING btree (user_id);

CREATE TABLE composer_requests (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    queue_name text NOT NULL,
    request jsonb NOT NULL,
    response jsonb NOT NULL,
    response_time_ms integer NOT NULL,
    uuid text DEFAULT gen_random_uuid() NOT NULL UNIQUE
);
CREATE INDEX composer_requests_user_id_idx ON composer_requests USING btree (user_id);

CREATE TABLE composer_choices (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    request_id integer REFERENCES composer_requests (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    choice jsonb NOT NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE conductor_requests (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    request text,
    context json,
    response json,
    response_time_ms integer,
    "timestamp" timestamp without time zone DEFAULT now(),
    tag_version text,
    session_context json
);
CREATE INDEX conductor_requests_user_id_idx ON conductor_requests USING btree (user_id);

CREATE TABLE conductor_request_tags (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    conductor_request_id integer REFERENCES conductor_requests (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    category text NOT NULL,
    value text NOT NULL,
    tag_version text,
    UNIQUE (conductor_request_id, tag_version, category, value)
);

CREATE TABLE conductor_token_quota (
    minute integer PRIMARY KEY,
    tokens_used integer NOT NULL
);

CREATE TABLE config_vals (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name text UNIQUE,
    value text
);

CREATE TABLE devices (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL,
    author_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
    code text NOT NULL,
    superseded_by integer REFERENCES devices (id) ON UPDATE CASCADE ON DELETE SET NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    uuid text DEFAULT gen_random_uuid() NOT NULL UNIQUE
);
CREATE INDEX devices_author_id_idx ON devices USING btree (author_id);

CREATE TABLE device_tags (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    device_id integer REFERENCES devices (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    value text NOT NULL,
    category text,
    UNIQUE (device_id, category, value)
);
CREATE INDEX device_tags_device_id_idx ON device_tags USING btree (device_id);

CREATE TABLE embedded_strings (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    content text NOT NULL UNIQUE,
    embedding json NOT NULL
);

CREATE TABLE presets (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL,
    settings json NOT NULL,
    device_id integer REFERENCES devices (id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
    superseded_by integer REFERENCES presets (id) ON UPDATE CASCADE ON DELETE SET NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    author_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
    uuid text DEFAULT gen_random_uuid() NOT NULL UNIQUE
);
CREATE INDEX presets_author_id_idx ON presets USING btree (author_id);
CREATE INDEX presets_device_id_idx ON presets USING btree (device_id);

CREATE TABLE preset_tags (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    preset_id integer REFERENCES presets (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    value text NOT NULL,
    category text,
    UNIQUE (preset_id, category, value)
);

CREATE TABLE sample_packs (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL,
    author_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    uuid text DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    published boolean DEFAULT false
);
CREATE INDEX sample_packs_author_id_idx ON sample_packs USING btree (author_id);

CREATE TABLE samples (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL,
    s3_key text NOT NULL,
    length_in_ms integer,
    pack_id integer REFERENCES sample_packs (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    author_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    uuid text DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    waveform_svg text,
    warp json,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    bpm integer
);
CREATE INDEX samples_author_id_idx ON samples USING btree (author_id);
CREATE INDEX samples_pack_id_idx ON samples USING btree (pack_id);

CREATE TABLE sample_tags (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sample_id integer REFERENCES samples (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    value text NOT NULL,
    category text,
    UNIQUE (sample_id, category, value)
);
CREATE INDEX samples_tags_sample_id_idx ON sample_tags USING btree (sample_id);

CREATE TABLE signup_surveys (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    answers json NOT NULL
);
CREATE INDEX signup_surveys_user_id_idx ON signup_surveys USING btree (user_id);

CREATE TABLE subscription_updates (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    subscribed_until timestamp without time zone NOT NULL,
    amount_paid_cents_usd integer DEFAULT 0 NOT NULL,
    "timestamp" timestamp without time zone DEFAULT now(),
    will_renew boolean DEFAULT true
);
CREATE INDEX subscription_updates_user_id_idx ON subscription_updates USING btree (user_id);

CREATE TABLE user_identities (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id integer REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE NOT NULL,
    issuer text NOT NULL,
    external_id text NOT NULL,
    profile_data text
);
CREATE INDEX user_identities_iss_ext_id_idx ON user_identities USING btree (issuer, external_id);
CREATE INDEX user_identities_user_id_idx ON user_identities USING btree (user_id);

CREATE TABLE ecs_scalers (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE,
    cluster_arn text NOT NULL,
    service_arn text NOT NULL,
    min_workers integer NOT NULL,
    max_workers integer NOT NULL,
    cooldown_seconds integer NOT NULL,
    last_scale_time timestamp without time zone,
    UNIQUE (cluster_arn, service_arn),
    CHECK (min_workers >= 0 AND min_workers <= max_workers AND cooldown_seconds >= 0)
);
