script.sql

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;


DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306010311_versao_inicial') THEN
    CREATE TABLE "Clientes" (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY,
        "Limite" integer NOT NULL,
        "Saldo" integer NOT NULL,
        CONSTRAINT "PK_Clientes" PRIMARY KEY ("Id")
    );
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306010311_versao_inicial') THEN
    INSERT INTO "Clientes" ("Id", "Limite", "Saldo")
    VALUES (1, 100000, 0);
    INSERT INTO "Clientes" ("Id", "Limite", "Saldo")
    VALUES (2, 80000, 0);
    INSERT INTO "Clientes" ("Id", "Limite", "Saldo")
    VALUES (3, 1000000, 0);
    INSERT INTO "Clientes" ("Id", "Limite", "Saldo")
    VALUES (4, 10000000, 0);
    INSERT INTO "Clientes" ("Id", "Limite", "Saldo")
    VALUES (5, 500000, 0);
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306010311_versao_inicial') THEN
    PERFORM setval(
        pg_get_serial_sequence('"Clientes"', 'Id'),
        GREATEST(
            (SELECT MAX("Id") FROM "Clientes") + 1,
            nextval(pg_get_serial_sequence('"Clientes"', 'Id'))),
        false);
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306010311_versao_inicial') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240306010311_versao_inicial', '8.0.2');
    END IF;
END $EF$;
COMMIT;

START TRANSACTION;


DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    ALTER TABLE "Clientes" DROP CONSTRAINT "PK_Clientes";
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    ALTER TABLE "Clientes" RENAME TO clientes;
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    ALTER TABLE clientes ADD CONSTRAINT "PK_clientes" PRIMARY KEY ("Id");
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    CREATE TABLE transacoes (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY,
        "ClienteId" integer NOT NULL,
        "Data" timestamp with time zone NOT NULL,
        "Valor" integer NOT NULL,
        "Tipo" character varying(1) NOT NULL,
        "Descricao" character varying(10) NOT NULL,
        CONSTRAINT "PK_transacoes" PRIMARY KEY ("Id"),
        CONSTRAINT "FK_transacoes_clientes_ClienteId" FOREIGN KEY ("ClienteId") REFERENCES clientes ("Id") ON DELETE CASCADE
    );
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    CREATE INDEX "IX_transacoes_ClienteId" ON transacoes ("ClienteId");
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240306101043_adiciona_transacoes') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240306101043_adiciona_transacoes', '8.0.2');
    END IF;
END $EF$;
COMMIT;