-- Verdant Meridian local development schema and seed data
-- Target database: investment | Target test user only: 18
-- Run with: mysql -u root investment < schema.sql
-- This script never creates a user account. It updates and seeds only user_id 18.

USE investment;
SET NAMES utf8mb4;
START TRANSACTION;

-- ---------------------------------------------------------------------------
-- Missing platform tables
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    reference VARCHAR(64) NOT NULL,
    transaction_type ENUM('deposit','withdrawal','investment','earning','adjustment') NOT NULL,
    direction ENUM('credit','debit') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status ENUM('pending','approved','declined','completed','cancelled') NOT NULL DEFAULT 'pending',
    description VARCHAR(500) NULL,
    source_table VARCHAR(64) NULL,
    source_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_transactions_reference (reference),
    KEY idx_transactions_user_created (user_id, created_at DESC),
    KEY idx_transactions_user_status (user_id, status),
    CONSTRAINT fk_transactions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- All new ledger rows are denominated in pounds sterling.
ALTER TABLE transactions ALTER currency SET DEFAULT 'GBP';

-- Legacy columns named *_usd_amount are retained for endpoint compatibility;
-- after this migration their values are interpreted and displayed as GBP.

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    category ENUM('account','deposit','withdrawal','investment','security','support') NOT NULL DEFAULT 'account',
    title VARCHAR(160) NOT NULL,
    body TEXT NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_notifications_user_read_created (user_id, is_read, created_at DESC),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS support_tickets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    ticket_number VARCHAR(32) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    category ENUM('account','deposit','withdrawal','investment','technical','other') NOT NULL DEFAULT 'other',
    priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
    status ENUM('open','in_progress','waiting_on_user','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_admin_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_support_ticket_number (ticket_number),
    KEY idx_support_tickets_user_status (user_id, status, created_at DESC),
    CONSTRAINT fk_support_tickets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS support_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ticket_id BIGINT UNSIGNED NOT NULL,
    sender_type ENUM('user','admin','system') NOT NULL,
    sender_id BIGINT UNSIGNED NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_support_messages_ticket_created (ticket_id, created_at),
    CONSTRAINT fk_support_messages_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    actor_type ENUM('user','admin','system') NOT NULL,
    actor_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    metadata JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_entity (entity_type, entity_id, created_at DESC),
    KEY idx_audit_actor (actor_type, actor_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Each investment can be credited at maturity exactly once. The unique key is
-- the guard used by the server-side maturity processor against double payouts.
CREATE TABLE IF NOT EXISTS investment_payouts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    investment_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    principal_amount DECIMAL(12,2) NOT NULL,
    return_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    credited_amount DECIMAL(12,2) NOT NULL,
    status ENUM('credited','failed') NOT NULL DEFAULT 'credited',
    processed_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_investment_payout_investment (investment_id),
    KEY idx_investment_payouts_user_processed (user_id, processed_at DESC),
    CONSTRAINT fk_investment_payouts_investment FOREIGN KEY (investment_id) REFERENCES investments(id) ON DELETE CASCADE,
    CONSTRAINT fk_investment_payouts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Periodic return credits. A payment can be processed only once per period.
CREATE TABLE IF NOT EXISTS investment_return_payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    investment_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    period_number INT UNSIGNED NOT NULL,
    rate_percent DECIMAL(5,2) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    due_date DATETIME NOT NULL,
    status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_investment_return_period (investment_id, period_number),
    KEY idx_return_payments_due (status, due_date),
    KEY idx_return_payments_user_paid (user_id, paid_at DESC),
    CONSTRAINT fk_return_payments_investment FOREIGN KEY (investment_id) REFERENCES investments(id) ON DELETE CASCADE,
    CONSTRAINT fk_return_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------------
-- Repeatable local seed: user 18 only
-- Existing manual/production data is untouched. Only prior [SEED] rows for
-- this user are removed before seed rows are inserted again.
-- ---------------------------------------------------------------------------

DELETE FROM audit_logs
 WHERE actor_type = 'user' AND actor_id = 18 AND event_type LIKE 'seed.%';
DELETE FROM notifications
 WHERE user_id = 18 AND title LIKE '[SEED]%';
DELETE FROM support_messages
 WHERE message LIKE '[SEED]%'
   AND ticket_id IN (SELECT id FROM support_tickets WHERE user_id = 18 AND ticket_number LIKE 'SEED-%');
DELETE FROM support_tickets WHERE user_id = 18 AND ticket_number LIKE 'SEED-%';
DELETE FROM transactions WHERE user_id = 18 AND reference LIKE 'SEED-%';
DELETE FROM activity_logs WHERE user_id = 18 AND description LIKE '[SEED]%';
DELETE FROM earnings WHERE user_id = 18 AND description LIKE '[SEED]%';
DELETE rp FROM investment_return_payments rp
 INNER JOIN investments i ON i.id = rp.investment_id
 WHERE i.user_id = 18 AND i.package_name LIKE '[SEED]%';
DELETE p FROM investment_payouts p
 INNER JOIN investments i ON i.id = p.investment_id
 WHERE i.user_id = 18 AND i.package_name LIKE '[SEED]%';
DELETE FROM investments WHERE user_id = 18 AND package_name LIKE '[SEED]%';
DELETE FROM withdrawals WHERE user_id = 18 AND wallet_address LIKE 'seed-%';
DELETE FROM deposits WHERE user_id = 18 AND proof_file LIKE 'seed_%';

-- The available balance is intentionally set for UI testing.
UPDATE users
   SET account_balance = 8450.00,
       account_status = 'approved'
 WHERE id = 18;

-- Deposits: one approved record, one pending review record.
INSERT INTO deposits
    (user_id, usd_amount, crypto_amount, crypto_type, wallet_address, proof_file, status, created_at)
VALUES
    (18, 12000.00, 0.20000000, 'BTC', 'seed-btc-deposit-wallet', 'seed_btc_receipt.pdf', 'approved', DATE_SUB(NOW(), INTERVAL 42 DAY)),
    (18, 800.00, 800.00000000, 'USDT', 'seed-usdt-deposit-wallet', 'seed_usdt_receipt.png', 'pending', DATE_SUB(NOW(), INTERVAL 2 DAY));

-- Withdrawals: one completed/approved example and one pending review example.
INSERT INTO withdrawals
    (user_id, withdrawal_type, usd_amount, crypto_amount, crypto_type, wallet_address, status, created_at)
VALUES
    (18, 'crypto', 1050.00, 0.03500000, 'ETH', 'seed-eth-recipient-wallet', 'approved', DATE_SUB(NOW(), INTERVAL 20 DAY)),
    (18, 'crypto', 325.00, 325.00000000, 'USDT', 'seed-usdt-recipient-wallet', 'pending', DATE_SUB(NOW(), INTERVAL 1 DAY));

-- Investments have zero stated ROI in seed data. They exercise display states
-- without implying a guaranteed or simulated investment return.
INSERT INTO investments
    (user_id, package_name, amount, roi_percent, expected_return, start_date, end_date, status, created_at)
VALUES
    (18, '[SEED] Digital assets review', 2500.00, 1.00, 2600.00, DATE_SUB(NOW(), INTERVAL 30 DAY), DATE_ADD(NOW(), INTERVAL 90 DAY), 'active', DATE_SUB(NOW(), INTERVAL 30 DAY)),
    (18, '[SEED] Precious metals review', 1800.00, 1.00, 1908.00, DATE_SUB(NOW(), INTERVAL 14 DAY), DATE_ADD(NOW(), INTERVAL 166 DAY), 'active', DATE_SUB(NOW(), INTERVAL 14 DAY)),
    (18, '[SEED] Real assets review', 950.00, 0.75, 1007.00, DATE_SUB(NOW(), INTERVAL 120 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY), 'completed', DATE_SUB(NOW(), INTERVAL 120 DAY));

INSERT INTO investment_return_payments (investment_id, user_id, period_number, rate_percent, amount, due_date, status, paid_at)
SELECT id, user_id, 1, 1.00, 25.00, DATE_SUB(NOW(), INTERVAL 1 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Digital assets review';

INSERT INTO investment_return_payments (investment_id, user_id, period_number, rate_percent, amount, due_date, status)
SELECT id, user_id, 2, 1.00, 25.00, DATE_ADD(NOW(), INTERVAL 30 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Digital assets review'
UNION ALL SELECT id, user_id, 3, 1.00, 25.00, DATE_ADD(NOW(), INTERVAL 60 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Digital assets review'
UNION ALL SELECT id, user_id, 4, 1.00, 25.00, DATE_ADD(NOW(), INTERVAL 90 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Digital assets review';

INSERT INTO investment_return_payments (investment_id, user_id, period_number, rate_percent, amount, due_date, status)
SELECT id, user_id, 1, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 16 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review'
UNION ALL SELECT id, user_id, 2, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 46 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review'
UNION ALL SELECT id, user_id, 3, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 76 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review'
UNION ALL SELECT id, user_id, 4, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 106 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review'
UNION ALL SELECT id, user_id, 5, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 136 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review'
UNION ALL SELECT id, user_id, 6, 1.00, 18.00, DATE_ADD(NOW(), INTERVAL 166 DAY), 'pending'
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Precious metals review';

INSERT INTO investment_return_payments (investment_id, user_id, period_number, rate_percent, amount, due_date, status, paid_at)
SELECT id, user_id, 1, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 92 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 92 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 2, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 62 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 62 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 3, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 32 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 32 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 4, 0.75, 7.11, DATE_SUB(NOW(), INTERVAL 2 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 2 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 5, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 1 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 6, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 1 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 7, 0.75, 7.13, DATE_SUB(NOW(), INTERVAL 1 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review'
UNION ALL SELECT id, user_id, 8, 0.75, 7.11, DATE_SUB(NOW(), INTERVAL 1 DAY), 'paid', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review';

-- A completed seed record with an explicit, one-time credit so the completed
-- investment state and payout view can be tested without waiting for maturity.
INSERT INTO investment_payouts (investment_id, user_id, principal_amount, return_amount, credited_amount, status, processed_at)
SELECT id, user_id, amount, 57.00, amount, 'credited', DATE_SUB(NOW(), INTERVAL 2 DAY)
  FROM investments WHERE user_id = 18 AND package_name = '[SEED] Real assets review';

INSERT INTO earnings (user_id, amount, description, created_at) VALUES
    (18, 125.00, '[SEED] Posted account adjustment for dashboard testing.', DATE_SUB(NOW(), INTERVAL 8 DAY)),
    (18, 45.00, '[SEED] Posted account adjustment for transaction testing.', DATE_SUB(NOW(), INTERVAL 3 DAY));

INSERT INTO activity_logs (user_id, actor, action_type, description, created_at) VALUES
    (18, 'system', 'deposit', '[SEED] Approved BTC deposit recorded for dashboard testing.', DATE_SUB(NOW(), INTERVAL 42 DAY)),
    (18, 'user', 'investment', '[SEED] Digital assets review investment created.', DATE_SUB(NOW(), INTERVAL 30 DAY)),
    (18, 'user', 'withdrawal', '[SEED] USDT withdrawal request awaiting review.', DATE_SUB(NOW(), INTERVAL 1 DAY));

INSERT INTO transactions
    (user_id, reference, transaction_type, direction, amount, status, description, created_at)
VALUES
    (18, 'SEED-DEP-001', 'deposit', 'credit', 12000.00, 'approved', '[SEED] Approved BTC deposit.', DATE_SUB(NOW(), INTERVAL 42 DAY)),
    (18, 'SEED-INV-001', 'investment', 'debit', 2500.00, 'completed', '[SEED] Digital assets review allocation.', DATE_SUB(NOW(), INTERVAL 30 DAY)),
    (18, 'SEED-WDR-001', 'withdrawal', 'debit', 325.00, 'pending', '[SEED] USDT withdrawal request.', DATE_SUB(NOW(), INTERVAL 1 DAY));

INSERT INTO notifications (user_id, category, title, body, is_read, created_at) VALUES
    (18, 'deposit', '[SEED] Deposit approved', 'Your seeded BTC deposit is available for UI testing.', 1, DATE_SUB(NOW(), INTERVAL 42 DAY)),
    (18, 'withdrawal', '[SEED] Withdrawal pending', 'Your seeded USDT withdrawal request is awaiting review.', 0, DATE_SUB(NOW(), INTERVAL 1 DAY));

INSERT INTO support_tickets (user_id, ticket_number, subject, category, priority, status, created_at)
VALUES (18, 'SEED-00018', '[SEED] Deposit review question', 'deposit', 'normal', 'open', DATE_SUB(NOW(), INTERVAL 1 DAY));

INSERT INTO support_messages (ticket_id, sender_type, sender_id, message, created_at)
SELECT id, 'user', 18, '[SEED] I am testing the support-ticket conversation view.', DATE_SUB(NOW(), INTERVAL 1 DAY)
  FROM support_tickets WHERE ticket_number = 'SEED-00018';

INSERT INTO audit_logs (actor_type, actor_id, event_type, entity_type, entity_id, metadata, created_at)
VALUES ('user', 18, 'seed.loaded', 'user', 18, JSON_OBJECT('purpose', 'local UI testing'), NOW());

COMMIT;

-- Useful post-seed checks
SELECT id, full_name, account_balance, account_status FROM users WHERE id = 18;
SELECT 'deposits' AS source, status, COUNT(*) AS records, SUM(usd_amount) AS usd_total FROM deposits WHERE user_id = 18 GROUP BY status
UNION ALL SELECT 'withdrawals', status, COUNT(*), SUM(usd_amount) FROM withdrawals WHERE user_id = 18 GROUP BY status
UNION ALL SELECT 'investments', status, COUNT(*), SUM(amount) FROM investments WHERE user_id = 18 GROUP BY status;
