-- ============================================================
-- FintechWerx – Database Setup
-- Run this once in phpMyAdmin or MySQL CLI:
--   mysql -u root fintechwerx < setup_db.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS `fintechwerx`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `fintechwerx`;

CREATE TABLE IF NOT EXISTS `customer_cards` (
    `id`              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `customer_id`     INT UNSIGNED   NOT NULL DEFAULT 1,
    `vault_token`     VARCHAR(255)   NOT NULL,
    `cardholder_name` VARCHAR(100)   NOT NULL,
    `card_last4`      CHAR(4)        NOT NULL,
    `card_expiry`     VARCHAR(10)    DEFAULT NULL,
    `card_type`       VARCHAR(20)    DEFAULT NULL,
    `is_active`       TINYINT(1)     NOT NULL DEFAULT 1,
    `created_at`      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_is_active`   (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NOTE: CVV is NEVER stored — PCI DSS strictly prohibits it.