-- перенос клиентов в отдельную базу данных

-- 1 - делаем бекап таблицы клиентов

-- 2 - удаляем все связи

ALTER TABLE bill.bill_clients DROP CONSTRAINT bill_bill_clients_client_id_fkey;

ALTER TABLE bill.bills DROP CONSTRAINT bill_bills_client_id_fkey;

ALTER TABLE bill.online_orders DROP CONSTRAINT bill_online_orders_client_id_fkey;

ALTER TABLE restaurant.client_cards DROP CONSTRAINT restaurant_client_cards_client_id_fkey;

ALTER TABLE restaurant.client_restaurant_codes DROP CONSTRAINT restaurant_client_restaurant_codes_client_id_fkey;

ALTER TABLE restaurant.client_restaurants DROP CONSTRAINT restaurant_client_restaurants_client_id_fkey;

ALTER TABLE restaurant.table_reserveds DROP CONSTRAINT restaurant_table_reserveds_client_id_fkey;

DROP TRIGGER update_or_insert_sync_objects ON clients;

-- 3 - удаляем таблицу clients

-- 4 - создаем новую базу данных clients

-- 5 - создаем таблицу clients в новой базе данных
      CREATE TABLE clients
      (
        id uuid NOT NULL,
        first_name CHARACTER VARYING(100),
        last_name CHARACTER VARYING(50),
        patronymic CHARACTER VARYING(50),
        email CHARACTER VARYING(100),
        sex BOOLEAN DEFAULT TRUE,
        phone CHARACTER VARYING(50),
        birthday DATE,
        created_at TIMESTAMP WITHOUT TIME zone NOT NULL,
        updated_at TIMESTAMP WITHOUT TIME zone NOT NULL,
        is_activated BOOLEAN NOT NULL DEFAULT FALSE,
        sms_code CHARACTER VARYING(10),
        sms_code_send_at TIMESTAMP WITHOUT TIME zone,
        token CHARACTER VARYING(255),
        token_created_at CHARACTER VARYING(255),
        encrypted_password CHARACTER VARYING(255) NOT NULL DEFAULT ''::CHARACTER VARYING,
        password_salt CHARACTER VARYING(255),
        reset_password_token CHARACTER VARYING(255),
        reset_password_sent_at TIMESTAMP WITHOUT TIME zone,
        remember_created_at TIMESTAMP WITHOUT TIME zone,
        sign_in_count INTEGER DEFAULT 0,
        current_sign_in_at TIMESTAMP WITHOUT TIME zone,
        last_sign_in_at TIMESTAMP WITHOUT TIME zone,
        current_sign_in_ip CHARACTER VARYING(255),
        last_sign_in_ip CHARACTER VARYING(255),
        confirmation_token CHARACTER VARYING(255),
        confirmed_at TIMESTAMP WITHOUT TIME zone,
        confirmation_sent_at TIMESTAMP WITHOUT TIME zone,
        unconfirmed_email CHARACTER VARYING(255),
        authentication_token CHARACTER VARYING(255),
        CONSTRAINT clients_pkey PRIMARY KEY (id)
      )
      WITH (
        OIDS=FALSE
      );
      ALTER TABLE clients
        OWNER TO developer;

      -- Index: index_clients_on_authentication_token

      -- DROP INDEX index_clients_on_authentication_token;

      CREATE UNIQUE INDEX index_clients_on_authentication_token
        ON clients
        USING btree
        (authentication_token COLLATE pg_catalog."default");

      -- Index: index_clients_on_confirmation_token

      -- DROP INDEX index_clients_on_confirmation_token;

      CREATE UNIQUE INDEX index_clients_on_confirmation_token
        ON clients
        USING btree
        (confirmation_token COLLATE pg_catalog."default");

      -- Index: index_clients_on_email

      -- DROP INDEX index_clients_on_email;

      CREATE UNIQUE INDEX index_clients_on_email
        ON clients
        USING btree
        (email COLLATE pg_catalog."default");

      -- Index: index_clients_on_first_name

      -- DROP INDEX index_clients_on_first_name;

      CREATE INDEX index_clients_on_first_name
        ON clients
        USING btree
        (first_name COLLATE pg_catalog."default");

      -- Index: index_clients_on_is_activated

      -- DROP INDEX index_clients_on_is_activated;

      CREATE INDEX index_clients_on_is_activated
        ON clients
        USING btree
        (is_activated);

      -- Index: index_clients_on_reset_password_token

      -- DROP INDEX index_clients_on_reset_password_token;

      CREATE UNIQUE INDEX index_clients_on_reset_password_token
        ON clients
        USING btree
        (reset_password_token COLLATE pg_catalog."default");

      -- Index: index_clients_on_sms_code

      -- DROP INDEX index_clients_on_sms_code;

      CREATE UNIQUE INDEX index_clients_on_sms_code
        ON clients
        USING btree
        (sms_code COLLATE pg_catalog."default");

      -- Index: index_clients_on_token

      -- DROP INDEX index_clients_on_token;

      CREATE UNIQUE INDEX index_clients_on_token
        ON clients
        USING btree
        (token COLLATE pg_catalog."default");

-- 6 - восстанавливаем таблицу clients с бекапа таблицы
-- 7 - настраивам связь в старых базах данных на новою базу данных
    -- создание расширения
    CREATE EXTENSION postgres_fdw;

    -- добавление внешнего сервера
    CREATE SERVER clients_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', dbname 'clients' );

    -- отображения пользователя
    CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS ( password '' );

    -- создание внешней таблицы
    CREATE FOREIGN TABLE clients (
        id uuid NOT NULL,
        first_name CHARACTER VARYING(100),
        last_name CHARACTER VARYING(50),
        patronymic CHARACTER VARYING(50),
        email CHARACTER VARYING(100),
        sex BOOLEAN DEFAULT TRUE,
        phone CHARACTER VARYING(50),
        birthday DATE,
        created_at TIMESTAMP WITHOUT TIME zone NOT NULL,
        updated_at TIMESTAMP WITHOUT TIME zone NOT NULL,
        is_activated BOOLEAN NOT NULL DEFAULT FALSE,
        sms_code CHARACTER VARYING(10),
        sms_code_send_at TIMESTAMP WITHOUT TIME zone,
        token CHARACTER VARYING(255),
        token_created_at CHARACTER VARYING(255),
        encrypted_password CHARACTER VARYING(255) NOT NULL DEFAULT ''::CHARACTER VARYING,
        password_salt CHARACTER VARYING(255),
        reset_password_token CHARACTER VARYING(255),
        reset_password_sent_at TIMESTAMP WITHOUT TIME zone,
        remember_created_at TIMESTAMP WITHOUT TIME zone,
        sign_in_count INTEGER DEFAULT 0,
        current_sign_in_at TIMESTAMP WITHOUT TIME zone,
        last_sign_in_at TIMESTAMP WITHOUT TIME zone,
        current_sign_in_ip CHARACTER VARYING(255),
        last_sign_in_ip CHARACTER VARYING(255),
        confirmation_token CHARACTER VARYING(255),
        confirmed_at TIMESTAMP WITHOUT TIME zone,
        confirmation_sent_at TIMESTAMP WITHOUT TIME zone,
        unconfirmed_email CHARACTER VARYING(255),
        authentication_token CHARACTER VARYING(255)

     ) SERVER clients_server OPTIONS ( TABLE_NAME 'clients' );


-- 8 создаем связи внешних ключей для таблицы
    ALTER TABLE bill.bill_clients
      ADD CONSTRAINT bill_bill_clients_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;

    ALTER TABLE bill.bills
      ADD CONSTRAINT bill_bills_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION;

    ALTER TABLE bill.online_orders
      ADD CONSTRAINT bill_online_orders_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION;

    ALTER TABLE restaurant.client_cards
      ADD CONSTRAINT restaurant_client_cards_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;


    ALTER TABLE restaurant.client_restaurant_codes
      ADD CONSTRAINT restaurant_client_restaurant_codes_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;


    ALTER TABLE restaurant.client_restaurants
      ADD CONSTRAINT restaurant_client_restaurants_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;

    ALTER TABLE restaurant.table_reserveds
      ADD CONSTRAINT restaurant_table_reserveds_client_id_fkey FOREIGN KEY (client_id)
          REFERENCES clients (id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;

    CREATE TRIGGER update_or_insert_sync_objects
      AFTER INSERT OR UPDATE OR DELETE
      ON clients
      FOR EACH ROW
      EXECUTE PROCEDURE remove_from_device_send_xmls();