path: root/db/migrations.go

package db

type Migration struct {
	Version int64
	Up      []string
	Down    []string
}

var migrations []Migration = []Migration{
	{
		Version: 1,
		Up: []string{
			`CREATE TABLE users (
				id            BIGSERIAL PRIMARY KEY,
				email         text NOT NULL,
				password_hash text NOT NULL,
				otp_secret    text,
				is_otp_setup  boolean,
				status        smallint,
				UNIQUE(email)
			)`,
			`CREATE TABLE market_configs (
				id          BIGSERIAL PRIMARY KEY,
				market_name text NOT NULL,
				user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
				config      jsonb
			)`,
			`CREATE UNIQUE INDEX IF NOT EXISTS market_name_user_id_idx ON public.market_configs (user_id, market_name)`,
			`CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`,
			`CREATE TABLE reports (
				id               BIGSERIAL PRIMARY KEY,
				date             timestamp with time zone NOT NULL,
				market_config_id bigint NOT NULL REFERENCES market_configs(id),
				debug            boolean
			)`,
			"CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)",
			`CREATE TABLE report_lines (
				id        BIGSERIAL PRIMARY KEY,
				date      timestamp with time zone NOT NULL,
				report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
				type      text,
				payload   jsonb
			)`,
			"CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)",
			"CREATE INDEX IF NOT EXISTS report_lines_type      ON report_lines (type)",
			`CREATE VIEW view_report_lines_by_user AS
			    SELECT report_lines.id,
				reports.id AS report_id,
				market_configs.market_name,
				users.id AS user_id,
				users.email AS user_email,
				reports.date AS report_date,
				report_lines.date,
				report_lines.payload
			      FROM public.report_lines
				JOIN public.reports ON reports.id = report_lines.report_id
				JOIN public.market_configs ON reports.market_config_id = market_configs.id
				JOIN public.users ON market_configs.user_id = users.id`,
		},
		Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"},
	},
	{
		Version: 201805061000,
		Up: []string{
			`CREATE VIEW "view_balances" AS
					    SELECT report_lines.id,
						reports.id AS report_id,
						market_configs.market_name,
		                                market_configs.id AS market_id,
						reports.date AS report_date,
						report_lines.date,
						report_lines.payload
					      FROM report_lines
						JOIN reports ON reports.id = report_lines.report_id
						JOIN market_configs ON reports.market_config_id = market_configs.id
					      WHERE report_lines.payload::jsonb->>'checkpoint' IS NOT NULL`,
			`CREATE INDEX checkpoints_idx ON report_lines ((payload->>'checkpoint'))`,
		},
		Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"},
	},
	{
		Version: 201805101000,
		Up: []string{
			"CREATE TYPE market_config_status AS ENUM ('enabled', 'disabled', 'invalid_credentials')",
			"ALTER TABLE market_configs ADD status market_config_status NOT NULL DEFAULT 'disabled'",
		},
		Down: []string{
			"ALTER TABLE market_configs DROP COLUMN status",
			"DROP TYPE market_config_status",
		},
	},
	{
		Version: 201805131000,
		Up: []string{
			"CREATE TYPE user_role AS ENUM ('admin', 'user')",
			"ALTER TABLE users ADD role user_role NOT NULL DEFAULT 'user'",
		},
		Down: []string{
			"ALTER TABLE users DROP COLUMN role",
			"DROP TYPE user_role",
		},
	},
	{
		Version: 201807311000,
		Up: []string{
			"CREATE TYPE portfolio_profile AS ENUM ('high-liquidity', 'medium-liquidity')",
			`CREATE TABLE bot_settings (
				id                BIGSERIAL PRIMARY KEY,
				user_id           bigint REFERENCES users(id) ON DELETE CASCADE,
				portfolio_profile portfolio_profile
			)`,
			`CREATE INDEX IF NOT EXISTS bot_settings_user_id ON bot_settings (user_id)`,
			`CREATE UNIQUE INDEX bot_settings_unique_null_user_id ON bot_settings (coalesce(user_id, 0))`,
		},
		Down: []string{
			"DROP TABLE bot_settings",
			"DROP TYPE portfolio_profile",
		},
	},
	{
		Version: 201808031000,
		Up: []string{
			`CREATE VIEW market_configs_augmented AS
				SELECT market_configs.*,
					COALESCE(u.portfolio_profile, g.portfolio_profile)
						AS portfolio_profile
				FROM market_configs
					LEFT JOIN bot_settings AS u
						ON u.user_id = market_configs.user_id,
					(
						SELECT portfolio_profile
							FROM bot_settings
							WHERE user_id IS NULL
					) AS g`,
		},
		Down: []string{
			"DROP VIEW market_configs_augmented",
		},
	},
}