pratica_integridade_e_consistencia.sql

-- CREATE DATABASE pratica_integridade_e_consistencia;

CREATE TYPE customer_phone_type AS ENUM ('landline', 'mobile');
CREATE TYPE transaction_type AS ENUM ('deposit', 'withdraw');

CREATE TABLE
    customers (
        id SERIAL PRIMARY KEY,
        full_name VARCHAR(255) NOT NULL,
        cpf VARCHAR(11) NOT NULL UNIQUE,
        email VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL
    );

CREATE TABLE
    customers_phones (
        id SERIAL PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id),
        number VARCHAR(11) NOT NULL,
        type customer_phone_type NOT NULL
    );

CREATE TABLE
    states (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL UNIQUE
    );

CREATE TABLE 
    cities (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        state_id INTEGER NOT NULL REFERENCES states(id)
    );

CREATE TABLE
    customer_addresses (
        id SERIAL PRIMARY KEY,
        customer_id INTEGER NOT NULL UNIQUE REFERENCES customers(id),
        street VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        complement VARCHAR(255),
        postal_code VARCHAR(8) NOT NULL,
        city_id INTEGER NOT NULL REFERENCES cities(id)
    );

CREATE TABLE
    bank_account (
        id SERIAL PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id),
        account_number VARCHAR(255) NOT NULL UNIQUE,
        agency VARCHAR(255) NOT NULL,
        open_date DATE NOT NULL DEFAULT NOW(),
        close_date DATE
    );

CREATE TABLE
    transactions (
        id SERIAL PRIMARY KEY,
        bank_account_id INTEGER NOT NULL REFERENCES bank_account(id),
        amount INTEGER NOT NULL DEFAULT 0,
        type transaction_type NOT NULL,
        time TIMESTAMP NOT NULL DEFAULT NOW(),
        description VARCHAR(255),
        cancelled BOOLEAN NOT NULL DEFAULT FALSE
    );

CREATE TABLE
    credit_cards (
        id SERIAL PRIMARY KEY,
        bank_account_id INTEGER NOT NULL REFERENCES bank_account(id),
        name VARCHAR(255) NOT NULL,
        number VARCHAR(16) NOT NULL UNIQUE,
        security_code VARCHAR(3) NOT NULL,
        expiration_month INTEGER NOT NULL,
        expiration_year INTEGER NOT NULL,
        password VARCHAR(255) NOT NULL,
        "limit" INTEGER NOT NULL DEFAULT 0
    );