-- 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 `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 `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,
  `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 `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 `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 `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;

-- Add joint account reference to existing transactions table
ALTER TABLE `transactions` 
ADD COLUMN `joint_account_id` int(11) DEFAULT NULL AFTER `portfolio_id`,
ADD KEY `joint_account_id` (`joint_account_id`);

-- Add joint account reference to existing investments table
ALTER TABLE `investments` 
ADD COLUMN `joint_account_id` int(11) DEFAULT NULL AFTER `manager_id`,
ADD KEY `joint_account_id` (`joint_account_id`);

-- Add joint account reference to existing portfolios table
ALTER TABLE `portfolios` 
ADD COLUMN `joint_account_id` int(11) DEFAULT NULL AFTER `user_id`,
ADD KEY `joint_account_id` (`joint_account_id`);

-- Add joint account reference to existing payments table
ALTER TABLE `payments` 
ADD COLUMN `joint_account_id` int(11) DEFAULT NULL AFTER `user_id`,
ADD KEY `joint_account_id` (`joint_account_id`);

-- Add joint account settings to user_settings table
INSERT 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');
