-- Create partnership accounts system
-- This replaces the joint account system with a partnership-focused approach

-- Partnership accounts table (main account entity)
CREATE TABLE partnership_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    partnership_name VARCHAR(255) NOT NULL,
    account_type ENUM('funded', 'demo') DEFAULT 'funded',
    balance DECIMAL(15,2) DEFAULT 10000.00,
    status ENUM('active', 'suspended', 'closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Partnership members table (users who can access the partnership account)
CREATE TABLE partnership_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    partnership_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('primary', 'secondary', 'viewer') DEFAULT 'secondary',
    permissions JSON DEFAULT '["view", "trade", "withdraw"]',
    is_active BOOLEAN DEFAULT TRUE,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY partnership_user (partnership_id, user_id),
    FOREIGN KEY (partnership_id) REFERENCES partnership_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Partnership transactions table
CREATE TABLE partnership_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    partnership_id INT NOT NULL,
    type ENUM('trade', 'deposit', 'withdrawal') NOT NULL,
    symbol VARCHAR(20) NULL,
    quantity DECIMAL(15,8) NULL,
    price DECIMAL(15,8) NULL,
    side ENUM('buy', 'sell') NULL,
    amount DECIMAL(15,2) NOT NULL,
    initiated_by INT NOT NULL,
    status ENUM('completed', 'pending', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (partnership_id) REFERENCES partnership_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (initiated_by) REFERENCES users(id)
);

-- Partnership activity log
CREATE TABLE partnership_activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    partnership_id INT NOT NULL,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (partnership_id) REFERENCES partnership_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Partnership invitations table
CREATE TABLE partnership_invitations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    partnership_id INT NOT NULL,
    inviter_id INT NOT NULL,
    invitee_email VARCHAR(255) NOT NULL,
    invitation_token VARCHAR(64) UNIQUE NOT NULL,
    status ENUM('pending', 'accepted', 'expired') DEFAULT 'pending',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (partnership_id) REFERENCES partnership_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (inviter_id) REFERENCES users(id)
);

-- Add partnership_id to existing tables
ALTER TABLE transactions ADD COLUMN partnership_id INT NULL AFTER user_id;
ALTER TABLE investments ADD COLUMN partnership_id INT NULL AFTER user_id;
ALTER TABLE portfolios ADD COLUMN partnership_id INT NULL AFTER user_id;
ALTER TABLE payments ADD COLUMN partnership_id INT NULL AFTER user_id;

-- Add indexes for performance
CREATE INDEX idx_partnership_members_user ON partnership_members(user_id);
CREATE INDEX idx_partnership_members_partnership ON partnership_members(partnership_id);
CREATE INDEX idx_partnership_transactions_partnership ON partnership_transactions(partnership_id);
CREATE INDEX idx_partnership_transactions_symbol ON partnership_transactions(symbol);
CREATE INDEX idx_partnership_transactions_type_symbol ON partnership_transactions(type, symbol);
CREATE INDEX idx_partnership_invitations_token ON partnership_invitations(invitation_token);
CREATE INDEX idx_partnership_invitations_email ON partnership_invitations(invitee_email);

-- Insert default partnership account for testing
INSERT INTO partnership_accounts (account_number, partnership_name, account_type, balance) VALUES 
('PA2024001', 'Demo Partnership', 'funded', 10000.00);

-- Insert default partnership settings
INSERT INTO user_settings (user_id, setting_key, setting_value) VALUES 
(1, 'partnership_account_enabled', 'true'),
(1, 'partnership_trading_permissions', '["view", "trade", "withdraw"]');
