-- Joint Account Tables Migration
-- This migration adds support for joint accounts where users can invite and approve partners

-- Table for joint account invitations
CREATE TABLE IF NOT EXISTS `joint_account_invitations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inviter_id` int(11) NOT NULL,
  `invitee_email` varchar(255) NOT NULL,
  `invitee_name` varchar(255) DEFAULT NULL,
  `account_type` enum('funded','demo') NOT NULL DEFAULT 'funded',
  `permissions` json DEFAULT NULL,
  `status` enum('pending','accepted','declined','expired') DEFAULT 'pending',
  `invitation_token` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `inviter_id` (`inviter_id`),
  KEY `invitee_email` (`invitee_email`),
  KEY `status` (`status`),
  KEY `invitation_token` (`invitation_token`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Table for joint accounts
CREATE TABLE IF NOT EXISTS `joint_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_number` varchar(32) NOT NULL,
  `account_type` enum('funded','demo') NOT NULL DEFAULT 'funded',
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `approval_threshold` decimal(15,2) NOT NULL DEFAULT 1000.00,
  `status` enum('active','suspended','closed') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_number` (`account_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Table for joint account members (users who have access to the joint account)
CREATE TABLE IF NOT EXISTS `joint_account_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `joint_account_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role` enum('primary','secondary') NOT NULL DEFAULT 'secondary',
  `permissions` json DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `joint_account_user` (`joint_account_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `joint_account_id` (`joint_account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Table for joint account transactions
CREATE TABLE IF NOT EXISTS `joint_account_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `joint_account_id` int(11) NOT NULL,
  `initiated_by` int(11) NOT NULL,
  `type` enum('deposit','withdrawal','transfer','investment','trading') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending',
  `requires_approval` tinyint(1) DEFAULT 0,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `joint_account_id` (`joint_account_id`),
  KEY `initiated_by` (`initiated_by`),
  KEY `approved_by` (`approved_by`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Table for joint account activity log
CREATE TABLE IF NOT EXISTS `joint_account_activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `joint_account_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `joint_account_id` (`joint_account_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Helper procedure to safely add columns if they don't exist
DELIMITER //
CREATE PROCEDURE AddColumnIfNotExists(
    IN tableName VARCHAR(255),
    IN columnName VARCHAR(255),
    IN columnDef TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = tableName
        AND COLUMN_NAME = columnName
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '` ', columnDef);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- Also add index for joint_account_id
        SET @sqlIdx = CONCAT('ALTER TABLE `', tableName, '` ADD KEY `', columnName, '` (`', columnName, '`)');
        PREPARE stmtIdx FROM @sqlIdx;
        EXECUTE stmtIdx;
        DEALLOCATE PREPARE stmtIdx;
    END IF;
END //
DELIMITER ;

-- Add joint account reference to existing tables safely
CALL AddColumnIfNotExists('transactions', 'joint_account_id', 'int(11) DEFAULT NULL AFTER `portfolio_id`');
CALL AddColumnIfNotExists('investments', 'joint_account_id', 'int(11) DEFAULT NULL AFTER `manager_id`');
CALL AddColumnIfNotExists('portfolios', 'joint_account_id', 'int(11) DEFAULT NULL AFTER `user_id`');
CALL AddColumnIfNotExists('payments', 'joint_account_id', 'int(11) DEFAULT NULL AFTER `user_id`');

-- ALSO ENSURE existing joint_accounts table has the threshold column
CALL AddColumnIfNotExists('joint_accounts', 'approval_threshold', 'decimal(15,2) NOT NULL DEFAULT 1000.00 AFTER balance');

-- Clean up helper procedure
DROP PROCEDURE IF EXISTS AddColumnIfNotExists;

-- Add joint account settings to user_settings table
INSERT IGNORE INTO `user_settings` (`user_id`, `setting_key`, `setting_value`) VALUES
(1, 'joint_account_notifications', 'enabled'),
(1, 'joint_account_approval_required', 'true'),
(1, 'joint_account_max_members', '2');
