-- ============================================================
--  Fraud Alert Management System — Database Schema
--  Engine: MySQL 8.0+
-- ============================================================

CREATE DATABASE IF NOT EXISTS fraud_alert_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE fraud_alert_db;

-- -------------------------------------------------------
-- 1. USERS (Admin + Staff)
-- -------------------------------------------------------
CREATE TABLE users (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name    VARCHAR(120)  NOT NULL,
    email        VARCHAR(180)  NOT NULL UNIQUE,
    password     VARCHAR(255)  NOT NULL,          -- bcrypt hash
    role         ENUM('admin','staff') NOT NULL DEFAULT 'staff',
    department   VARCHAR(100)  NULL,
    phone        VARCHAR(20)   NULL,
    avatar       VARCHAR(255)  NULL,
    status       ENUM('active','suspended','inactive') NOT NULL DEFAULT 'active',
    last_login   DATETIME      NULL,
    failed_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME      NULL,
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role   (role),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 2. BLACKLISTED ACCOUNTS
-- -------------------------------------------------------
CREATE TABLE blacklisted_accounts (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_number  VARCHAR(30) NOT NULL UNIQUE,
    account_name    VARCHAR(120) NULL,
    bank_name       VARCHAR(120) NULL,
    reason          TEXT        NULL,
    added_by        INT UNSIGNED NOT NULL,
    added_at        DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_account (account_number)
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 3. FRAUD RULES
-- -------------------------------------------------------
CREATE TABLE fraud_rules (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rule_code   VARCHAR(50)  NOT NULL UNIQUE,   -- e.g. HIGH_AMOUNT, BLACKLISTED
    rule_name   VARCHAR(120) NOT NULL,
    description TEXT         NULL,
    threshold   DECIMAL(18,2) NULL,             -- numeric limit (amount, count)
    time_window INT UNSIGNED  NULL,             -- minutes window for freq checks
    severity    ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 4. TRANSACTIONS
-- -------------------------------------------------------
CREATE TABLE transactions (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    txn_reference    VARCHAR(40)  NOT NULL UNIQUE,  -- auto-generated
    txn_type         ENUM('debit','credit','transfer','withdrawal','deposit') NOT NULL,
    amount           DECIMAL(18,2) NOT NULL,
    currency         CHAR(3)      NOT NULL DEFAULT 'NGN',
    sender_account   VARCHAR(30)  NOT NULL,
    sender_name      VARCHAR(120) NULL,
    receiver_account VARCHAR(30)  NOT NULL,
    receiver_name    VARCHAR(120) NULL,
    bank_name        VARCHAR(120) NULL,
    description      TEXT         NULL,
    channel          ENUM('online','atm','branch','mobile','pos') NOT NULL DEFAULT 'online',
    status           ENUM('pending','completed','flagged','blocked','reversed') NOT NULL DEFAULT 'pending',
    recorded_by      INT UNSIGNED NOT NULL,
    recorded_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_sender   (sender_account),
    INDEX idx_receiver (receiver_account),
    INDEX idx_status   (status),
    INDEX idx_recorded (recorded_at)
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 5. FRAUD ALERTS
-- -------------------------------------------------------
CREATE TABLE fraud_alerts (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alert_code     VARCHAR(20)  NOT NULL UNIQUE,   -- e.g. ALT-2024-00001
    transaction_id INT UNSIGNED NOT NULL,
    rule_id        INT UNSIGNED NOT NULL,
    severity       ENUM('low','medium','high','critical') NOT NULL,
    description    TEXT         NOT NULL,
    status         ENUM('open','under_review','resolved','false_positive','escalated') NOT NULL DEFAULT 'open',
    assigned_to    INT UNSIGNED NULL,
    resolved_by    INT UNSIGNED NULL,
    resolved_at    DATETIME     NULL,
    resolution_note TEXT        NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (rule_id)        REFERENCES fraud_rules(id)  ON DELETE RESTRICT,
    FOREIGN KEY (assigned_to)    REFERENCES users(id)        ON DELETE SET NULL,
    FOREIGN KEY (resolved_by)    REFERENCES users(id)        ON DELETE SET NULL,
    INDEX idx_status   (status),
    INDEX idx_severity (severity),
    INDEX idx_created  (created_at)
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 6. ALERT COMMENTS / AUDIT TRAIL
-- -------------------------------------------------------
CREATE TABLE alert_comments (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alert_id   INT UNSIGNED NOT NULL,
    user_id    INT UNSIGNED NOT NULL,
    comment    TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (alert_id) REFERENCES fraud_alerts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id)        ON DELETE RESTRICT
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 7. ACTIVITY LOGS
-- -------------------------------------------------------
CREATE TABLE activity_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NULL,
    action      VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50)  NULL,   -- 'transaction','alert','user' etc.
    entity_id   INT UNSIGNED NULL,
    details     JSON         NULL,
    ip_address  VARCHAR(45)  NULL,
    user_agent  VARCHAR(255) NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user    (user_id),
    INDEX idx_action  (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- 8. SYSTEM NOTIFICATIONS
-- -------------------------------------------------------
CREATE TABLE notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    title      VARCHAR(160) NOT NULL,
    message    TEXT         NOT NULL,
    type       ENUM('alert','info','warning','success') NOT NULL DEFAULT 'info',
    is_read    TINYINT(1)   NOT NULL DEFAULT 0,
    link       VARCHAR(255) NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read)
) ENGINE=InnoDB;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default Admin (password: Admin@1234)
INSERT INTO users (full_name, email, password, role, department, status) VALUES
('System Administrator', 'admin@fraudalert.ng', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uSc/LgQkK', 'admin', 'IT Security', 'active'),
('Amina Bello', 'amina@fraudalert.ng', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uSc/LgQkK', 'staff', 'Operations', 'active'),
('Emeka Okafor', 'emeka@fraudalert.ng', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uSc/LgQkK', 'staff', 'Customer Service', 'active');

-- Fraud Rules
INSERT INTO fraud_rules (rule_code, rule_name, description, threshold, time_window, severity, is_active) VALUES
('HIGH_AMOUNT',       'High-Value Transaction',       'Transaction amount exceeds threshold', 500000.00,  NULL, 'high',     1),
('FREQ_TRANSACTION',  'Frequent Transactions',        'Too many transactions in a time window', 5.00,    30,   'medium',   1),
('BLACKLISTED_ACCT',  'Blacklisted Account',          'Transaction involves a blacklisted account', NULL, NULL, 'critical', 1),
('FAILED_LOGIN',      'Excessive Failed Login Attempts','Account has multiple failed login attempts', 3.00, 60, 'high',    1),
('ROUND_AMOUNT',      'Suspicious Round Amount',      'Unusually round large amounts may signal structuring', 100000.00, NULL, 'low', 1),
('OFF_HOURS',         'Off-Hours Transaction',        'Transactions outside business hours (8pm–6am)', NULL, NULL, 'low', 1),
('LARGE_CASH',        'Large Cash Withdrawal',        'Cash withdrawal above single-transaction limit', 300000.00, NULL, 'medium', 1);

-- Sample Blacklisted Accounts
INSERT INTO blacklisted_accounts (account_number, account_name, bank_name, reason, added_by) VALUES
('0123456789', 'Umar Suspicious Co.', 'First Bank', 'Linked to money laundering investigation', 1),
('9876543210', 'Ghost Ventures Ltd', 'Zenith Bank', 'Multiple fraud complaints', 1);