CREATE TABLE CLIENTE (
    id_cliente INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cpf CHAR(11) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    telefone VARCHAR(20),
    data_cadastro DATE NOT NULL
);

DESCRIBE CLIENTE;


CREATE TABLE CONTA (
    id_conta INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente_fk INT NOT NULL,
    tipo VARCHAR(20) NOT NULL,
    numero VARCHAR(20) NOT NULL UNIQUE,
    agencia VARCHAR(10) NOT NULL,
    saldo DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    tipo_conta VARCHAR(20) NOT NULL,
    status_conta VARCHAR(20) NOT NULL,
    score_risco DECIMAL(5,2),

    FOREIGN KEY (id_cliente_fk)
        REFERENCES CLIENTE(id_cliente)
);

DESCRIBE CONTA;

CREATE TABLE TRANSACAO (
    id_transacao INT AUTO_INCREMENT PRIMARY KEY,
    id_conta_origem_fk INT NOT NULL,
    id_conta_destino_fk INT,
    valor DECIMAL(15,2) NOT NULL,
    data_hora DATETIME NOT NULL,
    status_fraude VARCHAR(20) NOT NULL,
    fraud_score DECIMAL(5,2),
    ip_origem VARCHAR(45),
    device_id VARCHAR(100),

    FOREIGN KEY (id_conta_origem_fk)
        REFERENCES CONTA(id_conta),

    FOREIGN KEY (id_conta_destino_fk)
        REFERENCES CONTA(id_conta)
);

DESCRIBE TRANSACAO;

CREATE TABLE CREDITO (
    id_credito INT AUTO_INCREMENT PRIMARY KEY,
    id_conta_fk INT NOT NULL,
    limite_aprovado DECIMAL(15,2) NOT NULL,
    taxa_juros DECIMAL(5,2) NOT NULL,
    data_concessao DATE NOT NULL,
    status_credito VARCHAR(20) NOT NULL,

    FOREIGN KEY (id_conta_fk)
        REFERENCES CONTA(id_conta)
);

DESCRIBE CREDITO;


CREATE TABLE LOG_FRAUDE (
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    id_transacao_fk INT NOT NULL,
    fraud_score DECIMAL(5,2),
    motivo_bloqueio VARCHAR(255),
    data_hora DATETIME NOT NULL,

    FOREIGN KEY (id_transacao_fk)
        REFERENCES TRANSACAO(id_transacao)
);

DESCRIBE LOG_FRAUDE;



SHOW TABLES;



INSERT INTO CLIENTE
(nome, cpf, email, telefone, data_cadastro)
VALUES
('Joao Pedro', '12345678901', 'joao@email.com', '21999990001', '2026-01-10'),
('Maria Silva', '23456789012', 'maria@email.com', '21999990002', '2026-02-15'),
('Carlos Souza', '34567890123', 'carlos@email.com', '21999990003', '2026-03-20');

SELECT * FROM CLIENTE;

INSERT INTO CONTA
(id_cliente_fk, tipo, numero, agencia, saldo, tipo_conta, status_conta, score_risco)
VALUES
(1, 'Digital', '10001', '0001', 5000.00, 'Corrente', 'Ativa', 15.50),
(1, 'Digital', '10002', '0001', 2500.00, 'Poupanca', 'Ativa', 10.00),
(2, 'Digital', '20001', '0001', 8000.00, 'Corrente', 'Ativa', 25.00),
(3, 'Digital', '30001', '0001', 1200.00, 'Corrente', 'Ativa', 65.00);

SELECT * FROM CONTA;

INSERT INTO TRANSACAO
(id_conta_origem_fk, id_conta_destino_fk, valor,
 data_hora, status_fraude, fraud_score, ip_origem, device_id)
VALUES

(1, 3, 250.00, '2026-11-10 10:30:00',
 'Aprovada', 5.50, '192.168.0.10', 'DEVICE001'),

(2, NULL, 100.00, '2026-11-10 14:20:00',
 'Aprovada', 8.00, '192.168.0.11', 'DEVICE002'),

(3, 4, 300.00, '2026-11-11 09:15:00',
 'Pendente', 45.00, '192.168.0.12', 'DEVICE003'),

(4, 1, 900.00, '2026-11-12 18:45:00',
 'Bloqueada', 92.50, '192.168.0.13', 'DEVICE004');

SELECT * FROM TRANSACAO;


INSERT INTO CREDITO
(id_conta_fk, limite_aprovado, taxa_juros,
 data_concessao, status_credito)
VALUES
(1, 10000.00, 2.50, '2026-01-15', 'Ativo'),
(2, 5000.00, 1.80, '2026-02-20', 'Ativo'),
(3, 15000.00, 3.00, '2026-03-25', 'Ativo');

SELECT * FROM CREDITO;


INSERT INTO LOG_FRAUDE
(id_transacao_fk, fraud_score, motivo_bloqueio, data_hora)
VALUES
(4, 92.50, 'Score de fraude elevado',
 '2026-11-12 18:46:00');

SELECT * FROM LOG_FRAUDE;


CREATE VIEW vw_clientes_contas AS
SELECT
    c.id_cliente,
    c.nome,
    c.cpf,
    ct.id_conta,
    ct.numero,
    ct.agencia,
    ct.tipo_conta,
    ct.saldo,
    ct.status_conta
FROM CLIENTE c
INNER JOIN CONTA ct
    ON c.id_cliente = ct.id_cliente_fk;


SELECT * FROM vw_clientes_contas;


CREATE VIEW vw_transacoes_suspeitas AS
SELECT
    id_transacao,
    id_conta_origem_fk,
    id_conta_destino_fk,
    valor,
    data_hora,
    status_fraude,
    fraud_score,
    ip_origem
FROM TRANSACAO
WHERE status_fraude IN ('Pendente', 'Bloqueada');



SELECT * FROM vw_transacoes_suspeitas;


CREATE VIEW vw_historico_transacoes AS
SELECT
    t.id_transacao,
    cli_origem.nome AS cliente_origem,
    cli_destino.nome AS cliente_destino,
    t.valor,
    t.data_hora,
    t.status_fraude
FROM TRANSACAO t

INNER JOIN CONTA conta_origem
    ON t.id_conta_origem_fk = conta_origem.id_conta

INNER JOIN CLIENTE cli_origem
    ON conta_origem.id_cliente_fk = cli_origem.id_cliente

LEFT JOIN CONTA conta_destino
    ON t.id_conta_destino_fk = conta_destino.id_conta

LEFT JOIN CLIENTE cli_destino
    ON conta_destino.id_cliente_fk = cli_destino.id_cliente;


SELECT * FROM vw_historico_transacoes;