/*
 Navicat Premium Data Transfer

 Source Server         : Localhost Docker(PGSQL)
 Source Server Type    : PostgreSQL
 Source Server Version : 140009
 Source Host           : localhost:5432
 Source Catalog        : blockchain-dashboard
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 140009
 File Encoding         : 65001

 Date: 16/01/2024 12:22:20
*/


-- ----------------------------
-- Sequence structure for components_cmp_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."components_cmp_id_seq";
CREATE SEQUENCE "public"."components_cmp_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for dashboard_components_dbc_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."dashboard_components_dbc_id_seq";
CREATE SEQUENCE "public"."dashboard_components_dbc_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for dashboards_dbd_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."dashboards_dbd_id_seq";
CREATE SEQUENCE "public"."dashboards_dbd_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for databases_dbs_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."databases_dbs_id_seq";
CREATE SEQUENCE "public"."databases_dbs_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for queries_qry_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."queries_qry_id_seq";
CREATE SEQUENCE "public"."queries_qry_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for query_executions_qxn_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."query_executions_qxn_id_seq";
CREATE SEQUENCE "public"."query_executions_qxn_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Table structure for components
-- ----------------------------
DROP TABLE IF EXISTS "public"."components";
CREATE TABLE "public"."components" (
  "cmp_id" int4 NOT NULL DEFAULT nextval('components_cmp_id_seq'::regclass),
  "cmp_type" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "cmp_status" varchar(10) COLLATE "pg_catalog"."default",
  "cmp_options" jsonb,
  "cmp_created_at" timestamp(6),
  "cmp_updated_at" timestamp(6),
  "cmp_extra_options" jsonb
)
;

-- ----------------------------
-- Table structure for dashboard_components
-- ----------------------------
DROP TABLE IF EXISTS "public"."dashboard_components";
CREATE TABLE "public"."dashboard_components" (
  "dbc_id" int4 NOT NULL DEFAULT nextval('dashboard_components_dbc_id_seq'::regclass),
  "dbc_component_id" int4,
  "dbc_data_source" jsonb,
  "dbc_display_options" jsonb,
  "dbc_component_options" jsonb,
  "dbc_created_at" timestamp(6),
  "dbc_updated_at" timestamp(6),
  "dbc_component_extra_options" jsonb,
  "dbc_dashboard_id" int4 NOT NULL,
  "dbc_status" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Table structure for dashboards
-- ----------------------------
DROP TABLE IF EXISTS "public"."dashboards";
CREATE TABLE "public"."dashboards" (
  "dbd_id" int4 NOT NULL DEFAULT nextval('dashboards_dbd_id_seq'::regclass),
  "dbd_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "dbd_slug" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "dbd_status" varchar(10) COLLATE "pg_catalog"."default",
  "dbd_created_at" timestamp(6),
  "dbd_updated_at" timestamp(6),
  "dbd_options" jsonb
)
;

-- ----------------------------
-- Table structure for databases
-- ----------------------------
DROP TABLE IF EXISTS "public"."databases";
CREATE TABLE "public"."databases" (
  "dbs_id" int4 NOT NULL DEFAULT nextval('databases_dbs_id_seq'::regclass),
  "dbs_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "dbs_database" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "dbs_config" jsonb,
  "dbs_status" varchar(10) COLLATE "pg_catalog"."default",
  "dbs_created_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP
)
;

-- ----------------------------
-- Table structure for queries
-- ----------------------------
DROP TABLE IF EXISTS "public"."queries";
CREATE TABLE "public"."queries" (
  "qry_id" int4 NOT NULL DEFAULT nextval('queries_qry_id_seq'::regclass),
  "qry_title" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "qry_query" text COLLATE "pg_catalog"."default" NOT NULL,
  "qry_database_id" int4,
  "qry_status" varchar(10) COLLATE "pg_catalog"."default",
  "qry_created_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "qry_updated_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "qry_query_params" jsonb,
  "qry_options" jsonb
)
;


-- ----------------------------
-- Table structure for query_executions
-- ----------------------------
DROP TABLE IF EXISTS "public"."query_executions";
CREATE TABLE "public"."query_executions" (
  "qxn_id" int4 NOT NULL DEFAULT nextval('query_executions_qxn_id_seq'::regclass),
  "qxn_query_id" int4,
  "qxn_status" varchar(20) COLLATE "pg_catalog"."default",
  "qxn_submitted_at" timestamp(6),
  "qxn_started_at" timestamp(6),
  "qxn_ended_at" timestamp(6),
  "qxn_results" text COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."components_cmp_id_seq"
OWNED BY "public"."components"."cmp_id";
SELECT setval('"public"."components_cmp_id_seq"', 4, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."dashboard_components_dbc_id_seq"
OWNED BY "public"."dashboard_components"."dbc_id";
SELECT setval('"public"."dashboard_components_dbc_id_seq"', 6, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."dashboards_dbd_id_seq"
OWNED BY "public"."dashboards"."dbd_id";
SELECT setval('"public"."dashboards_dbd_id_seq"', 3, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."databases_dbs_id_seq"
OWNED BY "public"."databases"."dbs_id";
SELECT setval('"public"."databases_dbs_id_seq"', 3, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."queries_qry_id_seq"
OWNED BY "public"."queries"."qry_id";
SELECT setval('"public"."queries_qry_id_seq"', 7, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."query_executions_qxn_id_seq"
OWNED BY "public"."query_executions"."qxn_id";
SELECT setval('"public"."query_executions_qxn_id_seq"', 2, false);

-- ----------------------------
-- Checks structure for table components
-- ----------------------------
ALTER TABLE "public"."components" ADD CONSTRAINT "components_cmp_status_check" CHECK (cmp_status::text = ANY (ARRAY['active'::character varying, 'inactive'::character varying]::text[]));

-- ----------------------------
-- Primary Key structure for table components
-- ----------------------------
ALTER TABLE "public"."components" ADD CONSTRAINT "components_pkey" PRIMARY KEY ("cmp_id");

-- ----------------------------
-- Checks structure for table dashboard_components
-- ----------------------------
ALTER TABLE "public"."dashboard_components" ADD CONSTRAINT "dashboard_components_dbc_status_check" CHECK (dbc_status::text = ANY (ARRAY['active'::character varying::text, 'inactive'::character varying::text]));

-- ----------------------------
-- Primary Key structure for table dashboard_components
-- ----------------------------
ALTER TABLE "public"."dashboard_components" ADD CONSTRAINT "dashboard_components_pkey" PRIMARY KEY ("dbc_id");

-- ----------------------------
-- Uniques structure for table dashboards
-- ----------------------------
ALTER TABLE "public"."dashboards" ADD CONSTRAINT "dashboards_dbd_slug_key" UNIQUE ("dbd_slug");

-- ----------------------------
-- Checks structure for table dashboards
-- ----------------------------
ALTER TABLE "public"."dashboards" ADD CONSTRAINT "dashboards_dbd_status_check" CHECK (dbd_status::text = ANY (ARRAY['active'::character varying, 'inactive'::character varying]::text[]));

-- ----------------------------
-- Primary Key structure for table dashboards
-- ----------------------------
ALTER TABLE "public"."dashboards" ADD CONSTRAINT "dashboards_pkey" PRIMARY KEY ("dbd_id");

-- ----------------------------
-- Checks structure for table databases
-- ----------------------------
ALTER TABLE "public"."databases" ADD CONSTRAINT "databases_dbs_status_check" CHECK (dbs_status::text = ANY (ARRAY['active'::character varying, 'inactive'::character varying]::text[]));
ALTER TABLE "public"."databases" ADD CONSTRAINT "databases_dbs_database_check" CHECK (dbs_database::text = ANY (ARRAY['postgres'::character varying::text, 'mysql'::character varying::text, 'elastic'::character varying::text, 'redis'::character varying::text, 'dune'::character varying::text]));

-- ----------------------------
-- Primary Key structure for table databases
-- ----------------------------
ALTER TABLE "public"."databases" ADD CONSTRAINT "databases_pkey" PRIMARY KEY ("dbs_id");

-- ----------------------------
-- Checks structure for table queries
-- ----------------------------
ALTER TABLE "public"."queries" ADD CONSTRAINT "queries_qry_status_check" CHECK (qry_status::text = ANY (ARRAY['active'::character varying, 'inactive'::character varying]::text[]));

-- ----------------------------
-- Primary Key structure for table queries
-- ----------------------------
ALTER TABLE "public"."queries" ADD CONSTRAINT "queries_pkey" PRIMARY KEY ("qry_id");


-- ----------------------------
-- Checks structure for table query_executions
-- ----------------------------
ALTER TABLE "public"."query_executions" ADD CONSTRAINT "query_executions_qxn_status_check" CHECK (qxn_status::text = ANY (ARRAY['pending'::character varying, 'processing'::character varying, 'completed'::character varying, 'failed'::character varying]::text[]));

-- ----------------------------
-- Primary Key structure for table query_executions
-- ----------------------------
ALTER TABLE "public"."query_executions" ADD CONSTRAINT "query_executions_pkey" PRIMARY KEY ("qxn_id");

-- ----------------------------
-- Foreign Keys structure for table dashboard_components
-- ----------------------------
ALTER TABLE "public"."dashboard_components" ADD CONSTRAINT "dashboard_components_dbc_component_id_fkey" FOREIGN KEY ("dbc_component_id") REFERENCES "public"."components" ("cmp_id") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- ----------------------------
-- Foreign Keys structure for table queries
-- ----------------------------
ALTER TABLE "public"."queries" ADD CONSTRAINT "queries_qry_database_id_fkey" FOREIGN KEY ("qry_database_id") REFERENCES "public"."databases" ("dbs_id") ON DELETE NO ACTION ON UPDATE NO ACTION;


-- ----------------------------
-- Foreign Keys structure for table query_executions
-- ----------------------------
ALTER TABLE "public"."query_executions" ADD CONSTRAINT "query_executions_qxn_query_id_fkey" FOREIGN KEY ("qxn_query_id") REFERENCES "public"."queries" ("qry_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
