-- ============================================================
--  INVESTMENT PLATFORM — COMPLETE DATABASE SCHEMA
--  Database : ftsacfmz_fts
--  DB User  : ftsacfmz_fts
--  Engine   : InnoDB / utf8mb4
--  Currency : RWF
-- ============================================================
--  HOW TO IMPORT:
--   1. Open phpMyAdmin
--   2. Click "Import" tab
--   3. Choose this .sql file
--   4. Click "Go"
-- ============================================================

CREATE DATABASE IF NOT EXISTS ftsacfmz_fts
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE ftsacfmz_fts;

-- Optional: run via root if you need to grant privileges
-- GRANT ALL PRIVILEGES ON ftsacfmz_fts.* TO 'ftsacfmz_fts'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
-- FLUSH PRIVILEGES;

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
--  1. ADMIN USERS
-- ============================================================
DROP TABLE IF EXISTS admin_users;
CREATE TABLE admin_users (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username        VARCHAR(50)  NOT NULL UNIQUE,
  email           VARCHAR(100) NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  full_name       VARCHAR(100) DEFAULT NULL,
  avatar          VARCHAR(255) DEFAULT NULL,
  role            ENUM('super_admin','admin','moderator') NOT NULL DEFAULT 'admin',
  is_active       TINYINT(1)   NOT NULL DEFAULT 1,
  last_login_at   DATETIME     DEFAULT NULL,
  last_login_ip   VARCHAR(45)  DEFAULT NULL,
  created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  2. USERS  (mobile-only auth + PIN)
-- ============================================================
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile                VARCHAR(20)   NOT NULL UNIQUE,
  full_name             VARCHAR(100)  DEFAULT NULL,
  email                 VARCHAR(100)  DEFAULT NULL,
  country               VARCHAR(50)   DEFAULT 'Rwanda',
  pin_hash              VARCHAR(255)  DEFAULT NULL,
  balance               DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_earnings        DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_deposits        DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_withdrawals     DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  referral_code         VARCHAR(20)   NOT NULL UNIQUE,
  referred_by           BIGINT UNSIGNED DEFAULT NULL,
  status                ENUM('active','suspended','banned','pending') NOT NULL DEFAULT 'active',
  is_verified           TINYINT(1)    NOT NULL DEFAULT 0,
  failed_pin_attempts   INT           NOT NULL DEFAULT 0,
  pin_locked_until      DATETIME      DEFAULT NULL,
  last_login_at         DATETIME      DEFAULT NULL,
  last_login_ip         VARCHAR(45)   DEFAULT NULL,
  created_at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_referred_by FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_users_mobile (mobile),
  INDEX idx_users_referral_code (referral_code),
  INDEX idx_users_status (status),
  INDEX idx_users_referred_by (referred_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  3. REFERRALS  (Level 1 = 6%, Level 2 = 2%)
-- ============================================================
DROP TABLE IF EXISTS referrals;
CREATE TABLE referrals (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_id     BIGINT UNSIGNED NOT NULL,
  referred_id     BIGINT UNSIGNED NOT NULL,
  level           TINYINT         NOT NULL,
  created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_referrals_referrer FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_referrals_referred FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_referrals_referred (referred_id),
  INDEX idx_referrals_referrer (referrer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  4. INVESTMENT PLANS
-- ============================================================
DROP TABLE IF EXISTS investment_plans;
CREATE TABLE investment_plans (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100)   NOT NULL,
  amount          DECIMAL(15,2)  NOT NULL,
  daily_return    DECIMAL(15,2)  NOT NULL,
  duration_days   INT            NOT NULL DEFAULT 30,
  description     TEXT           DEFAULT NULL,
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  display_order   INT            NOT NULL DEFAULT 0,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_plans_active (is_active),
  INDEX idx_plans_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  5. USER INVESTMENTS
-- ============================================================
DROP TABLE IF EXISTS user_investments;
CREATE TABLE user_investments (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id             BIGINT UNSIGNED NOT NULL,
  plan_id             BIGINT UNSIGNED NOT NULL,
  amount              DECIMAL(15,2)  NOT NULL,
  daily_return        DECIMAL(15,2)  NOT NULL,
  total_days          INT            NOT NULL,
  days_earned         INT            NOT NULL DEFAULT 0,
  total_earned        DECIMAL(15,2)  NOT NULL DEFAULT 0.00,
  start_date          DATETIME       NOT NULL,
  end_date            DATETIME       DEFAULT NULL,
  last_earning_date   DATETIME       DEFAULT NULL,
  status              ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  created_at          TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_inv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_plan FOREIGN KEY (plan_id) REFERENCES investment_plans(id) ON DELETE RESTRICT,
  INDEX idx_inv_user (user_id),
  INDEX idx_inv_status (status),
  INDEX idx_inv_start (start_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  6. EARNINGS  (investment / referral / gift / bonus)
-- ============================================================
DROP TABLE IF EXISTS earnings;
CREATE TABLE earnings (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id             BIGINT UNSIGNED NOT NULL,
  investment_id       BIGINT UNSIGNED DEFAULT NULL,
  amount              DECIMAL(15,2)  NOT NULL,
  type                ENUM('investment','referral','gift','bonus','penalty') NOT NULL DEFAULT 'investment',
  description         VARCHAR(255)   DEFAULT NULL,
  reference_type      VARCHAR(50)    DEFAULT NULL,
  reference_id        BIGINT UNSIGNED DEFAULT NULL,
  created_at          TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_earn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_earn_inv  FOREIGN KEY (investment_id) REFERENCES user_investments(id) ON DELETE SET NULL,
  INDEX idx_earn_user (user_id),
  INDEX idx_earn_type (type),
  INDEX idx_earn_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  7. REFERRAL EARNINGS
-- ============================================================
DROP TABLE IF EXISTS referral_earnings;
CREATE TABLE referral_earnings (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_id           BIGINT UNSIGNED NOT NULL,
  referred_id           BIGINT UNSIGNED NOT NULL,
  source_investment_id  BIGINT UNSIGNED DEFAULT NULL,
  base_amount           DECIMAL(15,2)  NOT NULL,
  percentage            DECIMAL(5,2)   NOT NULL,
  amount                DECIMAL(15,2)  NOT NULL,
  level                 TINYINT        NOT NULL,
  created_at            TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_re_referrer FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_re_referred FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_re_inv      FOREIGN KEY (source_investment_id) REFERENCES user_investments(id) ON DELETE SET NULL,
  INDEX idx_re_referrer (referrer_id),
  INDEX idx_re_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  8. PAYMENT METHODS  (Mobile Money, Bank, etc.)
-- ============================================================
DROP TABLE IF EXISTS payment_methods;
CREATE TABLE payment_methods (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100)  NOT NULL,
  type            ENUM('mobile_money','bank','crypto','other') NOT NULL DEFAULT 'mobile_money',
  account_number  VARCHAR(100)  DEFAULT NULL,
  account_name    VARCHAR(100)  DEFAULT NULL,
  instructions    TEXT          DEFAULT NULL,
  is_active       TINYINT(1)    NOT NULL DEFAULT 1,
  display_order   INT           NOT NULL DEFAULT 0,
  created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  9. DEPOSITS  (admin must approve/reject)
-- ============================================================
DROP TABLE IF EXISTS deposits;
CREATE TABLE deposits (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id               BIGINT UNSIGNED NOT NULL,
  amount                DECIMAL(15,2)  NOT NULL,
  payment_method_id     BIGINT UNSIGNED DEFAULT NULL,
  payment_method_name   VARCHAR(100)    DEFAULT NULL,
  sender_mobile         VARCHAR(20)     DEFAULT NULL,
  transaction_reference VARCHAR(100)    DEFAULT NULL,
  status                ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_note            TEXT            DEFAULT NULL,
  processed_by          BIGINT UNSIGNED DEFAULT NULL,
  processed_at          DATETIME        DEFAULT NULL,
  created_at            TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  updated_at            TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_dep_user    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_dep_method FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL,
  INDEX idx_dep_user (user_id),
  INDEX idx_dep_status (status),
  INDEX idx_dep_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  10. WITHDRAWALS  (admin must approve/reject)
-- ============================================================
DROP TABLE IF EXISTS withdrawals;
CREATE TABLE withdrawals (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id               BIGINT UNSIGNED NOT NULL,
  amount                DECIMAL(15,2)  NOT NULL,
  payment_method_id     BIGINT UNSIGNED DEFAULT NULL,
  payment_method_name   VARCHAR(100)    DEFAULT NULL,
  destination_account   VARCHAR(100)    DEFAULT NULL,
  destination_name      VARCHAR(100)    DEFAULT NULL,
  status                ENUM('pending','approved','rejected','processed') NOT NULL DEFAULT 'pending',
  admin_note            TEXT            DEFAULT NULL,
  processed_by          BIGINT UNSIGNED DEFAULT NULL,
  processed_at          DATETIME        DEFAULT NULL,
  created_at            TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  updated_at            TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_wd_user    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_wd_method FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL,
  INDEX idx_wd_user (user_id),
  INDEX idx_wd_status (status),
  INDEX idx_wd_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  11. GIFT CODES
-- ============================================================
DROP TABLE IF EXISTS gift_codes;
CREATE TABLE gift_codes (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30)    NOT NULL UNIQUE,
  value           DECIMAL(15,2)  NOT NULL,
  max_uses        INT            NOT NULL DEFAULT 1,
  used_count      INT            NOT NULL DEFAULT 0,
  expires_at      DATETIME       DEFAULT NULL,
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  created_by      BIGINT UNSIGNED DEFAULT NULL,
  notes           TEXT           DEFAULT NULL,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_gc_code (code),
  INDEX idx_gc_active (is_active),
  INDEX idx_gc_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  12. GIFT CODE REDEMPTIONS
-- ============================================================
DROP TABLE IF EXISTS gift_code_redemptions;
CREATE TABLE gift_code_redemptions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gift_code_id    BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NOT NULL,
  code            VARCHAR(30)     NOT NULL,
  value           DECIMAL(15,2)   NOT NULL,
  status          ENUM('success','failed') NOT NULL DEFAULT 'success',
  failure_reason  VARCHAR(255)    DEFAULT NULL,
  created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_gcr_gc   FOREIGN KEY (gift_code_id) REFERENCES gift_codes(id) ON DELETE RESTRICT,
  CONSTRAINT fk_gcr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_gcr_code (gift_code_id),
  INDEX idx_gcr_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  13. PIN RESETS
-- ============================================================
DROP TABLE IF EXISTS pin_resets;
CREATE TABLE pin_resets (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  token           VARCHAR(100)    NOT NULL UNIQUE,
  expires_at      DATETIME        NOT NULL,
  is_used         TINYINT(1)      NOT NULL DEFAULT 0,
  ip_address      VARCHAR(45)     DEFAULT NULL,
  created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_pr_token (token),
  INDEX idx_pr_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  14. ANNOUNCEMENTS
-- ============================================================
DROP TABLE IF EXISTS announcements;
CREATE TABLE announcements (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title           VARCHAR(200)   NOT NULL,
  content         TEXT           NOT NULL,
  type            ENUM('info','warning','success','maintenance') NOT NULL DEFAULT 'info',
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  is_pinned       TINYINT(1)     NOT NULL DEFAULT 0,
  created_by      BIGINT UNSIGNED DEFAULT NULL,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ann_active (is_active),
  INDEX idx_ann_pinned (is_pinned)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  15. SETTINGS  (logo, banners, system config)
-- ============================================================
DROP TABLE IF EXISTS settings;
CREATE TABLE settings (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key     VARCHAR(100)   NOT NULL UNIQUE,
  setting_value   TEXT           DEFAULT NULL,
  type            ENUM('text','number','boolean','image','json','textarea') NOT NULL DEFAULT 'text',
  description     VARCHAR(255)   DEFAULT NULL,
  updated_by      BIGINT UNSIGNED DEFAULT NULL,
  updated_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  16. TRANSACTIONS  (general ledger)
-- ============================================================
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  type            ENUM('deposit','withdrawal','investment','earning','referral','gift','bonus','penalty') NOT NULL,
  amount          DECIMAL(15,2)  NOT NULL,
  direction       ENUM('credit','debit') NOT NULL,
  balance_after   DECIMAL(15,2)  DEFAULT NULL,
  reference_type  VARCHAR(50)    DEFAULT NULL,
  reference_id    BIGINT UNSIGNED DEFAULT NULL,
  description     VARCHAR(255)   DEFAULT NULL,
  status          ENUM('pending','completed','failed') NOT NULL DEFAULT 'completed',
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tx_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tx_user (user_id),
  INDEX idx_tx_type (type),
  INDEX idx_tx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  17. ACTIVITY LOGS
-- ============================================================
DROP TABLE IF EXISTS activity_logs;
CREATE TABLE activity_logs (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED DEFAULT NULL,
  user_type       ENUM('user','admin') NOT NULL DEFAULT 'user',
  action          VARCHAR(100)   NOT NULL,
  description     TEXT           DEFAULT NULL,
  ip_address      VARCHAR(45)    DEFAULT NULL,
  user_agent      VARCHAR(255)   DEFAULT NULL,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_log_user (user_id, user_type),
  INDEX idx_log_action (action),
  INDEX idx_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  18. LOGIN ATTEMPTS  (brute-force protection)
-- ============================================================
DROP TABLE IF EXISTS login_attempts;
CREATE TABLE login_attempts (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile          VARCHAR(20)    DEFAULT NULL,
  ip_address      VARCHAR(45)    DEFAULT NULL,
  user_agent      VARCHAR(255)   DEFAULT NULL,
  success         TINYINT(1)     NOT NULL DEFAULT 0,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_la_mobile (mobile),
  INDEX idx_la_ip (ip_address),
  INDEX idx_la_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  19. NOTIFICATIONS
-- ============================================================
DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  title           VARCHAR(200)   NOT NULL,
  message         TEXT           NOT NULL,
  type            ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
  is_read         TINYINT(1)     NOT NULL DEFAULT 0,
  link            VARCHAR(255)   DEFAULT NULL,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user (user_id),
  INDEX idx_notif_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  20. BANNERS
-- ============================================================
DROP TABLE IF EXISTS banners;
CREATE TABLE banners (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title           VARCHAR(200)   DEFAULT NULL,
  image_url       VARCHAR(255)   NOT NULL,
  link_url        VARCHAR(255)   DEFAULT NULL,
  position        ENUM('home_top','home_middle','sidebar','login') NOT NULL DEFAULT 'home_top',
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  display_order   INT            NOT NULL DEFAULT 0,
  created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ban_position (position),
  INDEX idx_ban_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  DEFAULT DATA — INVESTMENT PLANS
-- ============================================================
INSERT INTO investment_plans (name, amount, daily_return, duration_days, description, is_active, display_order) VALUES
('Starter Plan',     15000.00,    700.00, 30, 'Entry-level investment plan', 1, 1),
('Bronze Plan',      40000.00,   1100.00, 30, 'Bronze tier investment plan', 1, 2),
('Silver Plan',      90000.00,   2000.00, 30, 'Silver tier investment plan', 1, 3),
('Gold Plan',       150000.00,   2800.00, 30, 'Gold tier investment plan',   1, 4),
('Platinum Plan',   250000.00,   5000.00, 30, 'Platinum tier investment',    1, 5),
('Diamond Plan',    700000.00,  15000.00, 30, 'Diamond tier investment',     1, 6),
('VIP Plan',       1300000.00,  50000.00, 30, 'VIP exclusive plan',          1, 7);

-- ============================================================
--  DEFAULT ADMIN USER
--  Username: admin
--  Password: Admin@2024
--  (bcrypt hash — change immediately after first login)
-- ============================================================
INSERT INTO admin_users (username, email, password_hash, full_name, role, is_active) VALUES
('admin', 'admin@ftsacfmz.rw',
 '$2y$10$E5.NpPQh8GzYQvJ0qXj5EeXQ1zL3pFq8J6sK9vN2mH4tY7bC8dW9a',
 'System Administrator', 'super_admin', 1);

-- ============================================================
--  DEFAULT PAYMENT METHODS
-- ============================================================
INSERT INTO payment_methods (name, type, account_number, account_name, instructions, is_active, display_order) VALUES
('MTN Mobile Money',  'mobile_money', '0780000000', 'FTS Investment', 'Send money to the number above, then submit deposit request with transaction ID.', 1, 1),
('Airtel Money',      'mobile_money', '0720000000', 'FTS Investment', 'Send money to the number above, then submit deposit request with transaction ID.', 1, 2),
('Bank Transfer (BK)', 'bank',        '00000-00000000-00', 'FTS Investment Ltd', 'Transfer to the bank account above and submit deposit with reference number.', 1, 3);

-- ============================================================
--  DEFAULT SYSTEM SETTINGS
-- ============================================================
INSERT INTO settings (setting_key, setting_value, type, description) VALUES
('site_name',           'FTS Investment',           'text',     'Platform name'),
('site_logo',           '/uploads/logo.png',        'image',    'Site logo URL'),
('site_banner',         '/uploads/banner.png',      'image',    'Default site banner'),
('site_favicon',        '/uploads/favicon.ico',     'image',    'Favicon URL'),
('currency',            'RWF',                      'text',     'Default currency code'),
('currency_symbol',     'FRw',                      'text',     'Currency display symbol'),
('referral_level_1',    '6',                        'number',   'Level 1 referral percentage'),
('referral_level_2',    '2',                        'number',   'Level 2 referral percentage'),
('min_deposit',         '1000',                     'number',   'Minimum deposit amount'),
('min_withdrawal',      '1000',                     'number',   'Minimum withdrawal amount'),
('max_withdrawal',      '5000000',                  'number',   'Maximum withdrawal amount per day'),
('withdrawal_fee',      '0',                        'number',   'Withdrawal fee percentage'),
('login_max_attempts',  '5',                        'number',   'Max login attempts before lock'),
('login_lock_minutes',  '15',                       'number',   'Account lock duration in minutes'),
('pin_max_attempts',    '3',                        'number',   'Max PIN attempts before lock'),
('pin_lock_minutes',    '30',                       'number',   'PIN lock duration in minutes'),
('timezone',            'Africa/Kigali',            'text',     'Default platform timezone'),
('maintenance_mode',    '0',                        'boolean',  'Enable maintenance mode'),
('contact_email',       'support@ftsacfmz.rw',      'text',     'Support contact email'),
('contact_phone',       '+250700000000',            'text',     'Support contact phone'),
('whatsapp_link',       'https://wa.me/250700000000','text',    'WhatsApp support link'),
('telegram_link',       '',                         'text',     'Telegram channel link'),
('footer_text',         '© 2024 FTS Investment. All rights reserved.', 'textarea', 'Footer copyright text'),
('investment_auto_complete', '1',                   'boolean',  'Auto-complete investments after duration');

-- ============================================================
--  DEFAULT ANNOUNCEMENT
-- ============================================================
INSERT INTO announcements (title, content, type, is_active, is_pinned, created_by) VALUES
('Welcome to FTS Investment!',
 'Start your investment journey today. Choose from our 7 flexible plans and earn daily returns directly to your wallet. Use your referral code to invite friends and earn up to 8% commission across two levels.',
 'success', 1, 1, 1);

-- ============================================================
--  USEFUL VIEWS
-- ============================================================
CREATE OR REPLACE VIEW v_user_dashboard AS
SELECT
  u.id,
  u.mobile,
  u.full_name,
  u.balance,
  u.total_earnings,
  u.total_deposits,
  u.total_withdrawals,
  u.referral_code,
  u.status,
  (SELECT COUNT(*) FROM user_investments ui WHERE ui.user_id = u.id AND ui.status = 'active') AS active_investments,
  (SELECT COUNT(*) FROM referrals r WHERE r.referrer_id = u.id) AS total_referrals,
  (SELECT COALESCE(SUM(amount),0) FROM referral_earnings re WHERE re.referrer_id = u.id) AS total_referral_earnings,
  (SELECT COUNT(*) FROM deposits d WHERE d.user_id = u.id AND d.status = 'pending') AS pending_deposits,
  (SELECT COUNT(*) FROM withdrawals w WHERE w.user_id = u.id AND w.status = 'pending') AS pending_withdrawals
FROM users u;

CREATE OR REPLACE VIEW v_pending_deposits AS
SELECT d.*, u.mobile, u.full_name
FROM deposits d
JOIN users u ON u.id = d.user_id
WHERE d.status = 'pending'
ORDER BY d.created_at DESC;

CREATE OR REPLACE VIEW v_pending_withdrawals AS
SELECT w.*, u.mobile, u.full_name, u.balance AS current_balance
FROM withdrawals w
JOIN users u ON u.id = w.user_id
WHERE w.status = 'pending'
ORDER BY w.created_at DESC;

CREATE OR REPLACE VIEW v_active_investments AS
SELECT ui.*, u.mobile, u.full_name, ip.name AS plan_name
FROM user_investments ui
JOIN users u ON u.id = ui.user_id
JOIN investment_plans ip ON ip.id = ui.plan_id
WHERE ui.status = 'active'
ORDER BY ui.start_date DESC;

CREATE OR REPLACE VIEW v_admin_stats AS
SELECT
  (SELECT COUNT(*) FROM users)                                                              AS total_users,
  (SELECT COUNT(*) FROM users WHERE status = 'active')                                      AS active_users,
  (SELECT COUNT(*) FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY))          AS new_users_week,
  (SELECT COALESCE(SUM(amount),0) FROM deposits WHERE status = 'approved')                  AS total_deposits_amount,
  (SELECT COALESCE(SUM(amount),0) FROM withdrawals WHERE status = 'approved')               AS total_withdrawals_amount,
  (SELECT COUNT(*) FROM deposits WHERE status = 'pending')                                  AS pending_deposits,
  (SELECT COUNT(*) FROM withdrawals WHERE status = 'pending')                               AS pending_withdrawals,
  (SELECT COUNT(*) FROM user_investments WHERE status = 'active')                           AS active_investments,
  (SELECT COALESCE(SUM(amount),0) FROM earnings WHERE type = 'investment')                  AS total_investment_earnings,
  (SELECT COALESCE(SUM(amount),0) FROM earnings WHERE type = 'referral')                    AS total_referral_earnings,
  (SELECT COUNT(*) FROM gift_codes)                                                         AS total_gift_codes,
  (SELECT COUNT(*) FROM gift_code_redemptions WHERE status = 'success')                     AS total_gift_redemptions;

-- ============================================================
--  STORED PROCEDURE — Process Daily Earnings
--  (call from cron job once per day)
-- ============================================================
DELIMITER //

DROP PROCEDURE IF EXISTS sp_process_daily_earnings//
CREATE PROCEDURE sp_process_daily_earnings()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_inv_id BIGINT;
  DECLARE v_user_id BIGINT;
  DECLARE v_amount DECIMAL(15,2);
  DECLARE v_daily_return DECIMAL(15,2);
  DECLARE v_total_days INT;
  DECLARE v_days_earned INT;

  DECLARE cur CURSOR FOR
    SELECT id, user_id, amount, daily_return, total_days, days_earned
    FROM user_investments
    WHERE status = 'active'
      AND (last_earning_date IS NULL OR DATE(last_earning_date) < CURDATE())
      AND DATE(start_date) <= CURDATE();

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_inv_id, v_user_id, v_amount, v_daily_return, v_total_days, v_days_earned;
    IF done THEN LEAVE read_loop; END IF;

    IF v_days_earned < v_total_days THEN
      -- Credit earnings
      UPDATE users SET balance = balance + v_daily_return,
                       total_earnings = total_earnings + v_daily_return
      WHERE id = v_user_id;

      -- Record earning
      INSERT INTO earnings (user_id, investment_id, amount, type, description, reference_type, reference_id)
      VALUES (v_user_id, v_inv_id, v_daily_return, 'investment',
              CONCAT('Daily earning - ', v_daily_return, ' RWF'), 'investment', v_inv_id);

      -- Record transaction
      INSERT INTO transactions (user_id, type, amount, direction, balance_after, reference_type, reference_id, description, status)
      SELECT v_user_id, 'earning', v_daily_return, 'credit', balance, 'investment', v_inv_id,
             CONCAT('Daily investment earning'), 'completed'
      FROM users WHERE id = v_user_id;

      -- Update investment
      UPDATE user_investments
      SET days_earned = days_earned + 1,
          total_earned = total_earned + v_daily_return,
          last_earning_date = NOW()
      WHERE id = v_inv_id;

      -- Check completion
      IF v_days_earned + 1 >= v_total_days THEN
        UPDATE user_investments SET status = 'completed', end_date = NOW() WHERE id = v_inv_id;
      END IF;

      -- Process Level 1 referral (6%)
      INSERT INTO referral_earnings (referrer_id, referred_id, source_investment_id, base_amount, percentage, amount, level)
      SELECT r.referrer_id, v_user_id, v_inv_id, v_daily_return, 6.00, ROUND(v_daily_return * 0.06, 2), 1
      FROM referrals r WHERE r.referred_id = v_user_id AND r.level = 1;

      UPDATE users u
      JOIN referrals r ON r.referrer_id = u.id
      SET u.balance = u.balance + ROUND(v_daily_return * 0.06, 2),
          u.total_earnings = u.total_earnings + ROUND(v_daily_return * 0.06, 2)
      WHERE r.referred_id = v_user_id AND r.level = 1;

      INSERT INTO earnings (user_id, investment_id, amount, type, description, reference_type, reference_id)
      SELECT r.referrer_id, v_inv_id, ROUND(v_daily_return * 0.06, 2), 'referral',
             CONCAT('Level 1 referral earning from user ', v_user_id), 'investment', v_inv_id
      FROM referrals r WHERE r.referred_id = v_user_id AND r.level = 1;

      -- Process Level 2 referral (2%)
      INSERT INTO referral_earnings (referrer_id, referred_id, source_investment_id, base_amount, percentage, amount, level)
      SELECT r.referrer_id, v_user_id, v_inv_id, v_daily_return, 2.00, ROUND(v_daily_return * 0.02, 2), 2
      FROM referrals r WHERE r.referred_id = v_user_id AND r.level = 2;

      UPDATE users u
      JOIN referrals r ON r.referrer_id = u.id
      SET u.balance = u.balance + ROUND(v_daily_return * 0.02, 2),
          u.total_earnings = u.total_earnings + ROUND(v_daily_return * 0.02, 2)
      WHERE r.referred_id = v_user_id AND r.level = 2;

      INSERT INTO earnings (user_id, investment_id, amount, type, description, reference_type, reference_id)
      SELECT r.referrer_id, v_inv_id, ROUND(v_daily_return * 0.02, 2), 'referral',
             CONCAT('Level 2 referral earning from user ', v_user_id), 'investment', v_inv_id
      FROM referrals r WHERE r.referred_id = v_user_id AND r.level = 2;
    END IF;
  END LOOP;
  CLOSE cur;
END//

-- ============================================================
--  STORED PROCEDURE — Approve Deposit
-- ============================================================
DROP PROCEDURE IF EXISTS sp_approve_deposit//
CREATE PROCEDURE sp_approve_deposit(IN p_deposit_id BIGINT, IN p_admin_id BIGINT)
BEGIN
  UPDATE deposits
  SET status = 'approved',
      processed_by = p_admin_id,
      processed_at = NOW()
  WHERE id = p_deposit_id AND status = 'pending';

  UPDATE users u
  JOIN deposits d ON d.user_id = u.id
  SET u.balance = u.balance + d.amount,
      u.total_deposits = u.total_deposits + d.amount
  WHERE d.id = p_deposit_id AND d.status = 'approved';

  INSERT INTO transactions (user_id, type, amount, direction, balance_after, reference_type, reference_id, description, status)
  SELECT d.user_id, 'deposit', d.amount, 'credit', u.balance, 'deposit', d.id,
         'Deposit approved', 'completed'
  FROM deposits d JOIN users u ON u.id = d.user_id
  WHERE d.id = p_deposit_id;
END//

-- ============================================================
--  STORED PROCEDURE — Approve Withdrawal
-- ============================================================
DROP PROCEDURE IF EXISTS sp_approve_withdrawal//
CREATE PROCEDURE sp_approve_withdrawal(IN p_withdrawal_id BIGINT, IN p_admin_id BIGINT)
BEGIN
  UPDATE withdrawals
  SET status = 'approved',
      processed_by = p_admin_id,
      processed_at = NOW()
  WHERE id = p_withdrawal_id AND status = 'pending';

  UPDATE users u
  JOIN withdrawals w ON w.user_id = u.id
  SET u.total_withdrawals = u.total_withdrawals + w.amount
  WHERE w.id = p_withdrawal_id AND w.status = 'approved';

  INSERT INTO transactions (user_id, type, amount, direction, balance_after, reference_type, reference_id, description, status)
  SELECT w.user_id, 'withdrawal', w.amount, 'debit', u.balance, 'withdrawal', w.id,
         'Withdrawal approved', 'completed'
  FROM withdrawals w JOIN users u ON u.id = w.user_id
  WHERE w.id = p_withdrawal_id;
END//

DELIMITER ;

-- ============================================================
--  END OF SQL FILE
-- ============================================================