-- Drop existing tables if they exist

DROP TABLE IF EXISTS "DP_API_CONTENT";
DROP TABLE IF EXISTS "DP_API_SUBSCRIPTION_POLICY";
DROP TABLE IF EXISTS "DP_ORGANIZATION_ASSETS";
DROP TABLE IF EXISTS "DP_API_IMAGEDATA";
DROP TABLE IF EXISTS "DP_API_METADATA";
DROP TABLE IF EXISTS "DP_IDENTITY_PROVIDER";
DROP TABLE IF EXISTS "DP_ORGANIZATION";
DROP TABLE IF EXISTS "DP_PROVIDER";


CREATE TABLE "DP_ORGANIZATION" (
	"ORG_ID" VARCHAR(256) NOT NULL,
	"ORG_NAME" VARCHAR(256) NOT NULL,
	"BUSINESS_OWNER" VARCHAR(256) NOT NULL,
	"BUSINESS_OWNER_CONTACT" VARCHAR(256) NOT NULL,
	"BUSINESS_OWNER_EMAIL" VARCHAR(256) NOT NULL,
    "DEV_PORTAL_URL_IDENTIFIER" VARCHAR(255),
    "ROLE_CLAIM_NAME" VARCHAR(255) NOT NULL,
    "GROUPS_CLAIM_NAME" VARCHAR(255) NOT NULL,
    "ORGANIZATION_CLAIM_NAME" VARCHAR(255) NOT NULL,
    "ORGANIZATION_IDENTIFIER" VARCHAR(255) NOT NULL,
    "ADMIN_ROLE" VARCHAR(255) NOT NULL,
    "SUBSCRIBER_ROLE" VARCHAR(255) NOT NULL,
    "SUPER_ADMIN_ROLE" VARCHAR(255) NOT NULL,
	UNIQUE ("ORG_NAME"),
	PRIMARY KEY("ORG_ID")
);

CREATE TABLE "DP_PROVIDER" (
    "ORG_ID" VARCHAR(256) NOT NULL,
    "NAME" VARCHAR(256) NOT NULL,
    "PROPERTY" VARCHAR(256) NOT NULL,
    "VALUE" VARCHAR(256) NOT NULL,
    UNIQUE ("NAME", "PROPERTY"),
    PRIMARY KEY("ORG_ID", "NAME", "PROPERTY")
);

CREATE TABLE "DP_ORGANIZATION_ASSETS" (
	"ASSERT_ID" VARCHAR(256) NOT NULL,
	"FILE_NAME" VARCHAR(256) NOT NULL,
	"FILE_CONTENT" BYTEA NOT NULL,
	"FILE_TYPE" VARCHAR(256) NOT NULL,
	"FILE_PATH" VARCHAR(1000) NOT NULL,
	"ORG_ID" VARCHAR(256) NOT NULL,
	FOREIGN KEY("ORG_ID") REFERENCES "DP_ORGANIZATION"("ORG_ID") ON DELETE CASCADE,
	UNIQUE ("FILE_NAME", "FILE_TYPE", "FILE_PATH", "ORG_ID"),
	PRIMARY KEY("ASSERT_ID")
);

CREATE TABLE IF NOT EXISTS "DP_IDENTITY_PROVIDER" (
    "ORG_ID" VARCHAR(256) NOT NULL,
    "NAME" VARCHAR(256) NOT NULL,
    "ISSUER" VARCHAR(256) NOT NULL,
    "AUTHORIZATION_URL" VARCHAR(256) NOT NULL,
    "TOKEN_URL" VARCHAR(256) NOT NULL,
    "USER_INFOR_URL" VARCHAR(256),
    "CLIENT_ID" VARCHAR(256) NOT NULL,
    "CALLBACK_URL" VARCHAR(256) NOT NULL,
    "SCOPE" VARCHAR(256) NOT NULL,
    "SIGNUP_URL" VARCHAR(256),
    "LOGOUT_URL" VARCHAR(256) NOT NULL,
    "LOGOUT_REDIRECT_URL" VARCHAR(256) NOT NULL,
    "JWKS_URL" VARCHAR(256),
    "CERTIFICATE" VARCHAR(5000),
    PRIMARY KEY ("ORG_ID"),
    FOREIGN KEY ("ORG_ID") REFERENCES "DP_ORGANIZATION"("ORG_ID") ON DELETE CASCADE
);

-- Create DP_API_METADATA table
CREATE TABLE "DP_API_METADATA" (
    "API_ID" VARCHAR(256) NOT NULL,
    "REFERENCE_ID" VARCHAR(256),
    "ORG_ID" VARCHAR(256) NOT NULL,
    "API_NAME" VARCHAR(256) NOT NULL,
    "API_DESCRIPTION" VARCHAR(256) NOT NULL,
    "API_VERSION" VARCHAR(256) NOT NULL,
    "API_TYPE" VARCHAR(256) NOT NULL,
    "VISIBILITY" VARCHAR(256) NOT NULL,
    "VISIBLE_GROUPS" VARCHAR(256),
    "TECHNICAL_OWNER" VARCHAR(256),
    "BUSINESS_OWNER" VARCHAR(256),
    "TECHNICAL_OWNER_EMAIL" VARCHAR(256),
    "BUSINESS_OWNER_EMAIL" VARCHAR(256),
    "PRODUCTION_URL" VARCHAR(256),
    "SANDBOX_URL" VARCHAR(256),
    "METADATA_SEARCH" JSON,
    "PROVIDER" VARCHAR(256),
    "TAGS" VARCHAR(256),
    UNIQUE ("ORG_ID", "API_NAME", "API_VERSION"),
    PRIMARY KEY ("API_ID"),
    FOREIGN KEY ("ORG_ID") REFERENCES "DP_ORGANIZATION"("ORG_ID") ON DELETE CASCADE
);

-- Create DP_API_CONTENT table
CREATE TABLE "DP_API_CONTENT" (
    "API_FILE" BYTEA,
    "FILE_NAME" VARCHAR(256) NOT NULL,
    "API_ID" VARCHAR(256) NOT NULL,
    PRIMARY KEY ("API_ID", "FILE_NAME"),
    FOREIGN KEY ("API_ID") REFERENCES "DP_API_METADATA"("API_ID") ON DELETE CASCADE
);

-- Create DP_API_IMAGEDATA table
CREATE TABLE "DP_API_IMAGEDATA" (
    "IMAGE_NAME" VARCHAR(256) NOT NULL,
    "IMAGE_TAG" VARCHAR(256) NOT NULL,
    "API_ID" VARCHAR(256) NOT NULL,
    PRIMARY KEY ("API_ID", "IMAGE_NAME", "IMAGE_TAG"),
    FOREIGN KEY ("API_ID") REFERENCES "DP_API_METADATA"("API_ID") ON DELETE CASCADE
);

-- Create DP_API_SUBSCRIPTION_POLICY table
CREATE TABLE "DP_API_SUBSCRIPTION_POLICY" (
    "POLICY_NAME" VARCHAR(256) NOT NULL,
    "API_ID" VARCHAR(256) NOT NULL,
    PRIMARY KEY ("API_ID", "POLICY_NAME"),
    FOREIGN KEY ("API_ID") REFERENCES "DP_API_METADATA"("API_ID") ON DELETE CASCADE
);
