books/database/DDL.sql

-- Active: 1766344152139@@db@5432@books
-- Active: 1766344152139@@db@5432@postgres

CREATE DATABASE Books;

CREATE SCHEMA IF NOT EXISTS "ingestion"

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


select * from cards;

CREATE TABLE public.cards
(
    id uuid NOT null DEFAULT uuid_generate_v1(),
    card_number varchar NOT NULL,
    card_holder varchar NOT NULL,
    expiration_date date NOT NULL,
    issuing_bank varchar NOT NULL,
    CONSTRAINT cards_pk PRIMARY KEY (id)
);

insert into public.cards (card_number, card_holder, expiration_date, issuing_bank) values
('4655', 'John Doe', '2025-12-31', 'Bank A'),
('5500000000000004', 'Jane Smith', '2024-11-30', 'Bank B'),
('340000000000009', 'Alice Johnson', '2026-10-31', 'Bank C');


DROP Table cards;

CREATE TABLE "ingestion".raw_transactions (
	id uuid NOT null DEFAULT uuid_generate_v1(),
	credit_card_id varchar NOT NULL,
	transaction_date date NOT NULL,
	merchant_name varchar NOT NULL,
	fx_rate decimal DEFAULT 1 NOT NULL,
	fx_amount decimal NOT NULL,
	isk_amount varchar NOT NULL,
	ingest_date date NOT null default CURRENT_DATE,
    raw JSON,
	CONSTRAINT cc_transactions_pk PRIMARY KEY (id)
);

-- Column comments

COMMENT ON COLUMN "ingestion".cc_transactions.credit_card_id IS 'Card id';




CREATE SCHEMA IF NOT EXISTS Staging;

DROP TABLE IF EXISTS Staging.CreditCards;
CREATE TABLE IF NOT EXISTS Staging.CreditCards
(
    TransactionID VARCHAR(150),
    CardID VARCHAR(159),
    CardOwnerID VARCHAR(10),
    CardHolderID VARCHAR(10),
    IssuerBranch VARCHAR(151),
    TransactionDate VARCHAR(152),
    ProcessingDate VARCHAR(153),
    Amount VARCHAR(154),
    ForeignAmount VARCHAR(155),
    AmountOrginal VARCHAR(156),
    ForeignAmountOrginal VARCHAR(157),
    CurrencyCode VARCHAR(158),
    TransactionKey VARCHAR(200),
    TransactionKeyDescription VARCHAR(500),
    TransactionTypeCode VARCHAR(29),
    TransactionTypeCodeDescription VARCHAR(100),
    MCC VARCHAR(101),
    MerchantType VARCHAR(102),
    MerchantName VARCHAR(200),
    MerchantCity VARCHAR(103),
    MerchantCountryCode VARCHAR(10),
    AuthorizationNumber VARCHAR(40),
    DueDate VARCHAR(159),
    SlipNumber VARCHAR(160),
    Reconciliation VARCHAR(161),
    SettlementReferenceCode VARCHAR(162),
    BilledStatus VARCHAR(10),
    IcelandairLoyaltyClubTransaction VARCHAR(10),
    ECardTransaction VARCHAR(10),
    SysDTCreate VARCHAR(163),
    PanID VARCHAR(20),
    IssuingProcessor VARCHAR(51)
);

COPY Staging.CreditCards(TransactionID,CardId,CardOwnerID,CardHolderID,IssuerBranch,TransactionDate,ProcessingDate,Amount,ForeignAmount,AmountOrginal,ForeignAmountOrginal,CurrencyCode,TransactionKey,TransactionKeyDescription,TransactionTypeCode,TransactionTypeCodeDescription,Mcc,MerchantType,MerchantName,MerchantCity,MerchantCountryCode,AuthorizationNumber,DueDate,SlipNumber,Reconciliation,SettlementReferenceCode,BilledStatus,IcelandairLoyaltyClubTransaction,eCardTransaction,SysDTCreate,PanId,IssuingProcessor)
FROM '/var/dataimport/creditcard.csv'
DELIMITER ';'r
CSV HEADER;





#CREATE TABLE IF NOT EXISTS Staging.Accounts,



SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;