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;

CREATE TABLE "Categories" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "CreatedDate" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Categories" PRIMARY KEY ("Id")
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230913144756_added_category', '7.0.11');

COMMIT;

START TRANSACTION;

CREATE TABLE "Products" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "Desciption" text NOT NULL,
    "ShopFavorites" boolean NOT NULL,
    "CustomerFavorites" boolean NOT NULL,
    "Color" text NOT NULL,
    "ImageUrl" text NOT NULL,
    "CategoryId" integer NOT NULL,
    CONSTRAINT "PK_Products" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Products_Categories_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "Categories" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_Products_CategoryId" ON "Products" ("CategoryId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230921141314_added_product', '7.0.11');

COMMIT;

START TRANSACTION;

ALTER TABLE "Products" RENAME COLUMN "Desciption" TO "Description";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230922162901_renamed_product_desc_column', '7.0.11');

COMMIT;

START TRANSACTION;

CREATE TABLE "ProductPrices" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "ProductId" integer NOT NULL,
    "Size" text NOT NULL,
    "Price" double precision NOT NULL,
    CONSTRAINT "PK_ProductPrices" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ProductPrices_Products_ProductId" FOREIGN KEY ("ProductId") REFERENCES "Products" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_ProductPrices_ProductId" ON "ProductPrices" ("ProductId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230924222151_added_product_price', '7.0.11');

COMMIT;

START TRANSACTION;

CREATE TABLE "AspNetRoles" (
    "Id" text NOT NULL,
    "Name" character varying(256) NULL,
    "NormalizedName" character varying(256) NULL,
    "ConcurrencyStamp" text NULL,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetUsers" (
    "Id" text NOT NULL,
    "UserName" character varying(256) NULL,
    "NormalizedUserName" character varying(256) NULL,
    "Email" character varying(256) NULL,
    "NormalizedEmail" character varying(256) NULL,
    "EmailConfirmed" boolean NOT NULL,
    "PasswordHash" text NULL,
    "SecurityStamp" text NULL,
    "ConcurrencyStamp" text NULL,
    "PhoneNumber" text NULL,
    "PhoneNumberConfirmed" boolean NOT NULL,
    "TwoFactorEnabled" boolean NOT NULL,
    "LockoutEnd" timestamp with time zone NULL,
    "LockoutEnabled" boolean NOT NULL,
    "AccessFailedCount" integer NOT NULL,
    CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);

CREATE TABLE "OrderDetails" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "OrderHeaderId" integer NOT NULL,
    "ProductId" integer NOT NULL,
    "Count" integer NOT NULL,
    "Price" double precision NOT NULL,
    "Size" text NOT NULL,
    "ProductName" text NOT NULL,
    CONSTRAINT "PK_OrderDetails" PRIMARY KEY ("Id")
);

CREATE TABLE "OrderHeaders" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "UserId" text NOT NULL,
    "OrderTotal" double precision NOT NULL,
    "OrderDate" timestamp with time zone NOT NULL,
    "ShippingDate" timestamp with time zone NOT NULL,
    "Status" text NOT NULL,
    "SessionId" text NULL,
    "PaymentIntentId" text NULL,
    "Name" text NOT NULL,
    "PhoneNumber" text NOT NULL,
    "StreetAddress" text NOT NULL,
    "State" text NOT NULL,
    "City" text NOT NULL,
    "PostalCode" text NOT NULL,
    "Email" text NOT NULL,
    CONSTRAINT "PK_OrderHeaders" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetRoleClaims" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "RoleId" text NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserClaims" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "UserId" text NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserLogins" (
    "LoginProvider" character varying(128) NOT NULL,
    "ProviderKey" character varying(128) NOT NULL,
    "ProviderDisplayName" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserRoles" (
    "UserId" text NOT NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserTokens" (
    "UserId" text NOT NULL,
    "LoginProvider" character varying(128) NOT NULL,
    "Name" character varying(128) NOT NULL,
    "Value" text NULL,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");

CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName");

CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");

CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");

CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");

CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230929154118_added_orderheaderdetails_and_identitytables', '7.0.11');

COMMIT;

START TRANSACTION;

ALTER TABLE "AspNetUsers" ADD "Discriminator" text NOT NULL DEFAULT '';

ALTER TABLE "AspNetUsers" ADD "Name" text NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230929160422_added_name_to_aspnetuser', '7.0.11');

COMMIT;

START TRANSACTION;

ALTER TABLE "OrderHeaders" ADD "Carrier" text NULL;

ALTER TABLE "OrderHeaders" ADD "Tracking" text NULL;

ALTER TABLE "AspNetUserTokens" ALTER COLUMN "Name" TYPE text;

ALTER TABLE "AspNetUserTokens" ALTER COLUMN "LoginProvider" TYPE text;

ALTER TABLE "AspNetUserLogins" ALTER COLUMN "ProviderKey" TYPE text;

ALTER TABLE "AspNetUserLogins" ALTER COLUMN "LoginProvider" TYPE text;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20231002214436_added_carrier_and_tracking_to_orderheader', '7.0.11');

COMMIT;