Bnei-Baruch/mdb

package cmd

import (
    "database/sql"

    "github.com/spf13/cobra"
    "github.com/spf13/viper"

    "github.com/Bnei-Baruch/mdb/importer/kmedia"
)

func init() {
    command := &cobra.Command{
        Use:   "kmedia-lessons",
        Short: "Import kmedia VirtualLessons to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportVirtualLessons()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-congresses",
        Short: "Import KMedia Congresses to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportCongresses()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-programs",
        Short: "Import KMedia Programs to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportProgramsChapters()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-lectures",
        Short: "Import KMedia Lectures Series to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportLectures()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-holidays",
        Short: "Import KMedia Holidays to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportHolidays()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-vls",
        Short: "Import KMedia VLs to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportVLs()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-clips",
        Short: "Import KMedia Clips to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportClips()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-flat",
        Short: "Import KMedia flat catalogs to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportFlatCatalogs()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-articles",
        Short: "Import KMedia articles to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportArticles()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-custom",
        Short: "Import KMedia custom containers to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportCustom()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-mixed-lessons",
        Short: "Import KMedia mixed lessons catalogs to MDB",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.ImportMixedLessons()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-map-units",
        Short: "Do unit mappings analysis",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.MapUnits()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-update",
        Short: "Update i18ns for collections and content units based on kmedia_id",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.UpdateI18ns()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-compare",
        Short: "Compare collections and content units to their equivalent counterparts in kmedia",
        Run: func(cmd *cobra.Command, args []string) {
            kmedia.Compare()
        },
    }
    RootCmd.AddCommand(command)

    command = &cobra.Command{
        Use:   "kmedia-fkeys",
        Short: "Add foreign keys to kmedia",
        Long:  "Add foreign keys to kmedia, then run:\n\tsqlboiler -o gmodels_old -p gmodels --no-hooks postgres",
        Run: func(cmd *cobra.Command, args []string) {
            createForeignKeys()
        },
    }
    RootCmd.AddCommand(command)
}

// go run main.go kmedia-fkeys
// sqlboiler -o gmodels_old -p gmodels --no-hooks --no-tests postgres
func createForeignKeys() {
    db, err := sql.Open("postgres", viper.GetString("kmedia.url"))
    if err != nil {
        panic(err)
    }
    defer db.Close()

    _, err = db.Exec(`

ALTER TABLE languages DROP CONSTRAINT IF EXISTS code3_unique CASCADE;
ALTER TABLE languages ADD CONSTRAINT code3_unique UNIQUE (code3);

ALTER TABLE catalogs DROP CONSTRAINT IF EXISTS catalogs_fkey;
ALTER TABLE catalogs ADD CONSTRAINT catalogs_fkey FOREIGN KEY (parent_id) REFERENCES catalogs(id) NOT VALID;
ALTER TABLE catalogs DROP CONSTRAINT IF EXISTS users_fkey;
ALTER TABLE catalogs ADD CONSTRAINT users_fkey FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

ALTER TABLE catalogs_containers DROP CONSTRAINT IF EXISTS catalogs_fkey;
ALTER TABLE catalogs_containers ADD CONSTRAINT catalogs_fkey FOREIGN KEY (catalog_id) REFERENCES catalogs(id) NOT VALID;
ALTER TABLE catalogs_containers DROP CONSTRAINT IF EXISTS containers_fkey;
ALTER TABLE catalogs_containers ADD CONSTRAINT containers_fkey FOREIGN KEY (container_id) REFERENCES containers(id) NOT VALID;

DO $$
    BEGIN
        ALTER TABLE catalog_descriptions RENAME COLUMN lang TO lang_id;
        EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%', 'Already Exists';
    END
$$ LANGUAGE plpgsql;
ALTER TABLE catalog_descriptions DROP CONSTRAINT IF EXISTS catalogs_fkey;
ALTER TABLE catalog_descriptions ADD CONSTRAINT catalogs_fkey FOREIGN KEY (catalog_id) REFERENCES catalogs(id) NOT VALID;
ALTER TABLE catalog_descriptions DROP CONSTRAINT IF EXISTS languages_fkey;
ALTER TABLE catalog_descriptions ADD CONSTRAINT languages_fkey FOREIGN KEY (lang_id) REFERENCES languages(code3) NOT VALID;

SELECT DISTINCT * INTO tmp FROM catalogs_container_description_patterns; DROP TABLE catalogs_container_description_patterns; SELECT * INTO catalogs_container_description_patterns FROM tmp; DROP TABLE tmp;
ALTER TABLE catalogs_container_description_patterns DROP CONSTRAINT IF EXISTS catalog_container_description_pattern_pkey;
ALTER TABLE catalogs_container_description_patterns ADD CONSTRAINT catalog_container_description_pattern_pkey PRIMARY KEY (catalog_id, container_description_pattern_id);
ALTER TABLE catalogs_container_description_patterns DROP CONSTRAINT IF EXISTS catalogs_fkey;
ALTER TABLE catalogs_container_description_patterns ADD CONSTRAINT catalogs_fkey FOREIGN KEY (catalog_id) REFERENCES catalogs(id) NOT VALID;
ALTER TABLE catalogs_container_description_patterns DROP CONSTRAINT IF EXISTS container_description_patterns_fkey;
ALTER TABLE catalogs_container_description_patterns ADD CONSTRAINT container_description_patterns_fkey FOREIGN KEY (container_description_pattern_id) REFERENCES container_description_patterns(id) NOT VALID;

DO $$
    BEGIN
        ALTER TABLE containers RENAME COLUMN lang TO lang_id;
        EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%', 'Already Exists';
    END
$$ LANGUAGE plpgsql;
ALTER TABLE containers DROP CONSTRAINT IF EXISTS languages_fkey;
ALTER TABLE containers ADD CONSTRAINT languages_fkey FOREIGN KEY (lang_id) REFERENCES languages(code3) NOT VALID;
ALTER TABLE containers DROP CONSTRAINT IF EXISTS content_types_fkey;
ALTER TABLE containers ADD CONSTRAINT content_types_fkey FOREIGN KEY (content_type_id) REFERENCES content_types(id) NOT VALID;
ALTER TABLE containers DROP CONSTRAINT IF EXISTS virtual_lessons_fkey;
ALTER TABLE containers ADD CONSTRAINT virtual_lessons_fkey FOREIGN KEY (virtual_lesson_id) REFERENCES virtual_lessons(id) NOT VALID;

DO $$
    BEGIN
        ALTER TABLE container_descriptions RENAME COLUMN lang TO lang_id;
        EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%', 'Already Exists';
    END
$$ LANGUAGE plpgsql;
ALTER TABLE container_descriptions DROP CONSTRAINT IF EXISTS languages_fkey;
ALTER TABLE container_descriptions ADD CONSTRAINT languages_fkey FOREIGN KEY (lang_id) REFERENCES languages(code3) NOT VALID;
ALTER TABLE container_descriptions DROP CONSTRAINT IF EXISTS container_descriptions_fkey;
ALTER TABLE container_descriptions ADD CONSTRAINT container_descriptions_fkey FOREIGN KEY (container_id) REFERENCES containers(id) NOT VALID;

ALTER TABLE containers_file_assets DROP CONSTRAINT IF EXISTS lessonfiles_pkey;
ALTER TABLE containers_file_assets DROP CONSTRAINT IF EXISTS containers_file_assets_pkey;
ALTER TABLE containers_file_assets ADD CONSTRAINT containers_file_assets_pkey PRIMARY KEY (container_id, file_asset_id);
ALTER TABLE containers_file_assets DROP CONSTRAINT IF EXISTS containers_fkey;
ALTER TABLE containers_file_assets ADD CONSTRAINT containers_fkey FOREIGN KEY (container_id) REFERENCES containers(id) NOT VALID;
ALTER TABLE containers_file_assets DROP CONSTRAINT IF EXISTS file_assets_fkey;
ALTER TABLE containers_file_assets ADD CONSTRAINT file_assets_fkey FOREIGN KEY (file_asset_id) REFERENCES file_assets(id) NOT VALID;

ALTER TABLE containers_labels DROP CONSTRAINT IF EXISTS container_label_pkey;
ALTER TABLE containers_labels ADD CONSTRAINT container_label_pkey PRIMARY KEY (label_id, container_id);
ALTER TABLE containers_labels DROP CONSTRAINT IF EXISTS containers_fkey;
ALTER TABLE containers_labels ADD CONSTRAINT containers_fkey FOREIGN KEY (container_id) REFERENCES containers(id) NOT VALID;
ALTER TABLE containers_labels DROP CONSTRAINT IF EXISTS labels_fkey;
ALTER TABLE containers_labels ADD CONSTRAINT labels_fkey FOREIGN KEY (label_id) REFERENCES labels(id) NOT VALID;

ALTER TABLE file_asset_descriptions DROP CONSTRAINT IF EXISTS file_asset_descriptions_fkey;
ALTER TABLE file_asset_descriptions ADD CONSTRAINT file_asset_descriptions_fkey FOREIGN KEY (file_id) REFERENCES file_assets(id) NOT VALID;

DO $$
    BEGIN
        ALTER TABLE file_assets RENAME COLUMN lang TO lang_id;
        EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%', 'Already Exists';
    END
$$ LANGUAGE plpgsql;
DO $$
    BEGIN
        ALTER TABLE file_assets RENAME COLUMN servername TO servername_id;
        EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%', 'Already Exists';
    END
$$ LANGUAGE plpgsql;
ALTER TABLE file_assets DROP CONSTRAINT IF EXISTS languages_fkey;
ALTER TABLE file_assets ADD CONSTRAINT languages_fkey FOREIGN KEY (lang_id) REFERENCES languages(code3) NOT VALID;
ALTER TABLE file_assets DROP CONSTRAINT IF EXISTS users_fkey;
ALTER TABLE file_assets ADD CONSTRAINT users_fkey FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE file_assets DROP CONSTRAINT IF EXISTS servers_fkey;
ALTER TABLE file_assets ADD CONSTRAINT servers_fkey FOREIGN KEY (servername_id) REFERENCES servers(servername) NOT VALID;

SELECT DISTINCT * INTO ru FROM roles_users; DROP TABLE roles_users; SELECT * INTO roles_users FROM ru; DROP TABLE ru;
ALTER TABLE roles_users DROP CONSTRAINT IF EXISTS role_user_pkey;
ALTER TABLE roles_users ADD CONSTRAINT role_user_pkey PRIMARY KEY (role_id, user_id);
ALTER TABLE roles_users DROP CONSTRAINT IF EXISTS roles_fkey;
ALTER TABLE roles_users ADD CONSTRAINT users_fkey FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
    `)
    if err != nil {
        panic(err)
    }
}