-- Set these yourself or let migrate do it for you
-- SET wavtool.production_db_host = '...';
-- SET wavtool.production_db_port = '...';
-- SET wavtool.production_db_password = '...';

-- Stop any other clone_prod operations that may be in progress
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name = 'clone_prod';
SET application_name TO clone_prod;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

BEGIN;

DROP SERVER IF EXISTS wavtool_production_server CASCADE;
DROP SCHEMA IF EXISTS wavtool_production CASCADE;

DO $$
BEGIN
  EXECUTE '
    CREATE SERVER wavtool_production_server
            FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (host ''' || current_setting('wavtool.production_db_host') || ''', port ''' || current_setting('wavtool.production_db_port') || ''', dbname ''wavtool'', fetch_size ''10000'')
  ';
  EXECUTE '
    CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER
            SERVER wavtool_production_server
            OPTIONS (user ''wavtool_readonly'', password ''' || current_setting('wavtool.production_db_password') || ''')
  ';
END
$$ LANGUAGE 'plpgsql';


CREATE SCHEMA wavtool_production;

IMPORT FOREIGN SCHEMA public
        LIMIT TO ( presets, preset_tags, devices, device_tags, samples, sample_tags, sample_packs, track_presets, faq_answers, faq_questions, voice_conversion_models, voice_conversion_model_training_runs, voice_conversion_model_corpus, sample_derivations, sample_voice_conversion_models )
        FROM SERVER wavtool_production_server
        INTO wavtool_production;

COMMIT;
BEGIN;

TRUNCATE TABLE presets, preset_tags, samples, sample_tags, sample_packs, devices, device_tags, track_presets, faq_answers, faq_questions, voice_conversion_models, voice_conversion_model_training_runs, voice_conversion_model_corpus, sample_derivations, sample_voice_conversion_models CASCADE;

CREATE OR REPLACE FUNCTION pg_temp.fix_id_sequence(table_name text) RETURNS void AS $$
DECLARE
  sequence_name text := table_name || '_id_seq';
  max_id_val integer;
BEGIN
  EXECUTE 'LOCK TABLE ' || table_name || ' IN EXCLUSIVE MODE';
  EXECUTE 'SELECT MAX(id)+1 FROM ' || table_name INTO max_id_val;
  PERFORM setval(sequence_name, COALESCE(max_id_val, 1), false);
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION pg_temp.raise_notice(s text) RETURNS void AS $$
BEGIN
    RAISE NOTICE '%', s;
END;
$$ LANGUAGE 'plpgsql';

INSERT INTO users
    (id, normalized_email, email, display_name)
  OVERRIDING SYSTEM VALUE
  VALUES
    (1, 'sam@wavtool.com', 'sam@wavtool.com', 'WavTool')
  ON CONFLICT (id) DO NOTHING;
SELECT pg_temp.fix_id_sequence('public.users');

SELECT pg_temp.raise_notice('Cloning devices...');
INSERT INTO devices
    (id, name, author_id, code, superseded_by, created_at, uuid)
  OVERRIDING SYSTEM VALUE
  SELECT id,name,1,code,superseded_by,created_at,uuid
    FROM wavtool_production.devices;
SELECT pg_temp.fix_id_sequence('public.devices');

SELECT pg_temp.raise_notice('Cloning device tags...');
INSERT INTO device_tags
    (id, device_id, category, value)
  OVERRIDING SYSTEM VALUE
  SELECT id, device_id, category, value
    FROM wavtool_production.device_tags;
SELECT pg_temp.fix_id_sequence('public.device_tags');

SELECT pg_temp.raise_notice('Cloning presets...');
INSERT INTO presets
    (id, name, settings, device_id, superseded_by, created_at, author_id, uuid)
  OVERRIDING SYSTEM VALUE
    SELECT id, name, settings, device_id, superseded_by, created_at, 1, uuid
      FROM wavtool_production.presets;
SELECT pg_temp.fix_id_sequence('public.presets');

SELECT pg_temp.raise_notice('Cloning preset tags...');
INSERT INTO preset_tags
    (id, preset_id, category, value)
  OVERRIDING SYSTEM VALUE
  SELECT id, preset_id, category, value
    FROM wavtool_production.preset_tags;
SELECT pg_temp.fix_id_sequence('public.preset_tags');

SELECT pg_temp.raise_notice('Cloning sample packs...');
INSERT INTO sample_packs
    (id, name, author_id, uuid, created_at, published)
  OVERRIDING SYSTEM VALUE
  SELECT id, name, 1, uuid, created_at, published
    FROM wavtool_production.sample_packs;
SELECT pg_temp.fix_id_sequence('public.sample_packs');

SELECT pg_temp.raise_notice('Cloning samples...');
INSERT INTO samples
    (id, name, s3_key, length_in_ms, pack_id, author_id, uuid, waveform_svg, warp, created_at, bpm, compressed_s3_key, analysis)
  OVERRIDING SYSTEM VALUE
  SELECT id, name, s3_key, length_in_ms, pack_id, 1, uuid, waveform_svg, warp, created_at, bpm, compressed_s3_key, analysis
    FROM wavtool_production.samples;
SELECT pg_temp.fix_id_sequence('public.samples');

SELECT pg_temp.raise_notice('Cloning sample tags...');
INSERT INTO sample_tags
    (id, sample_id, category, value)
  OVERRIDING SYSTEM VALUE
  SELECT id, sample_id, category, value
    FROM wavtool_production.sample_tags;
SELECT pg_temp.fix_id_sequence('public.sample_tags');

SELECT pg_temp.raise_notice('Cloning track presets...');
INSERT INTO track_presets
    (id, preset_data, created_at, updated_at, published)
  OVERRIDING SYSTEM VALUE
  SELECT id, preset_data, created_at, updated_at, published
    FROM wavtool_production.track_presets;
SELECT pg_temp.fix_id_sequence('public.track_presets');

SELECT pg_temp.raise_notice('Cloning FAQ answers...');
INSERT INTO faq_answers
    (id, answer)
  OVERRIDING SYSTEM VALUE
  SELECT id, answer
    FROM wavtool_production.faq_answers;
SELECT pg_temp.fix_id_sequence('public.faq_answers');

SELECT pg_temp.raise_notice('Cloning FAQ questions...');
INSERT INTO faq_questions
    (id, answer_id, question, embedding)
  OVERRIDING SYSTEM VALUE
  SELECT id, answer_id, question, embedding
    FROM wavtool_production.faq_questions;
SELECT pg_temp.fix_id_sequence('public.faq_questions');

SELECT pg_temp.raise_notice('Cloning voice conversion models...');
INSERT INTO voice_conversion_models
    (id, uuid, created_at, author_id, name, archived, updated_at)
  OVERRIDING SYSTEM VALUE
  SELECT id, uuid, created_at, null, name, archived, updated_at
    FROM wavtool_production.voice_conversion_models;
SELECT pg_temp.fix_id_sequence('public.voice_conversion_models');

SELECT pg_temp.raise_notice('Cloning voice conversion model training runs...');
INSERT INTO voice_conversion_model_training_runs
    (id, model_id, created_at, state, trainer_version, progress, s3_key, error, updated_at)
  OVERRIDING SYSTEM VALUE
  SELECT id, model_id, created_at, state, trainer_version, progress, s3_key, error, updated_at
    FROM wavtool_production.voice_conversion_model_training_runs;
SELECT pg_temp.fix_id_sequence('public.voice_conversion_model_training_runs');

SELECT pg_temp.raise_notice('Cloning voice conversion model corpus...');
INSERT INTO voice_conversion_model_corpus
    (model_id, sample_id, created_at)
  OVERRIDING SYSTEM VALUE
  SELECT model_id, sample_id, created_at
    FROM wavtool_production.voice_conversion_model_corpus;

SELECT pg_temp.raise_notice('Cloning sample derivations...');
INSERT INTO sample_derivations
    (sample_id, derived_from_id, derivation_type, created_at, updated_at)
  OVERRIDING SYSTEM VALUE
  SELECT sample_id, derived_from_id, derivation_type, created_at, updated_at
    FROM wavtool_production.sample_derivations;

SELECT pg_temp.raise_notice('Cloning sample voice conversion models...');
INSERT INTO sample_voice_conversion_models
    (sample_id, voice_conversion_model_id, created_at, updated_at)
  OVERRIDING SYSTEM VALUE
  SELECT sample_id, voice_conversion_model_id, created_at, updated_at
    FROM wavtool_production.sample_voice_conversion_models;


COMMIT;
BEGIN;

DROP SERVER wavtool_production_server CASCADE;
DROP SCHEMA wavtool_production CASCADE;

COMMIT;
