-- Schema: spider__customers_card_transactions
CREATE SCHEMA IF NOT EXISTS "spider__customers_card_transactions";

-- Table: Accounts
CREATE TABLE "spider__customers_card_transactions"."Accounts" (
    "account_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "account_name" TEXT,
    "other_account_details" TEXT,
    CONSTRAINT "Accounts_pkey" PRIMARY KEY ("account_id")
);

-- Table: Customers
CREATE TABLE "spider__customers_card_transactions"."Customers" (
    "customer_id" BIGINT NOT NULL,
    "customer_first_name" TEXT,
    "customer_last_name" TEXT,
    "customer_address" TEXT,
    "customer_phone" TEXT,
    "customer_email" TEXT,
    "other_customer_details" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("customer_id")
);

-- Table: Customers_Cards
CREATE TABLE "spider__customers_card_transactions"."Customers_Cards" (
    "card_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "card_type_code" TEXT NOT NULL,
    "card_number" TEXT,
    "date_valid_from" TIMESTAMP,
    "date_valid_to" TIMESTAMP,
    "other_card_details" TEXT,
    CONSTRAINT "Customers_Cards_pkey" PRIMARY KEY ("card_id")
);

-- Table: Financial_Transactions
CREATE TABLE "spider__customers_card_transactions"."Financial_Transactions" (
    "transaction_id" BIGINT NOT NULL,
    "previous_transaction_id" BIGINT,
    "account_id" BIGINT NOT NULL,
    "card_id" BIGINT NOT NULL,
    "transaction_type" TEXT NOT NULL,
    "transaction_date" TIMESTAMP,
    "transaction_amount" DOUBLE PRECISION,
    "transaction_comment" TEXT,
    "other_transaction_details" TEXT
);

-- Foreign Keys
ALTER TABLE "spider__customers_card_transactions"."Financial_Transactions" ADD CONSTRAINT "fk_Financial_Transactions_0" FOREIGN KEY ("account_id") REFERENCES "spider__customers_card_transactions"."Accounts" ("account_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_card_transactions"."Financial_Transactions" ADD CONSTRAINT "fk_Financial_Transactions_1" FOREIGN KEY ("card_id") REFERENCES "spider__customers_card_transactions"."Customers_Cards" ("card_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
