-- Messaging System Database Schema for Undatable.me
-- Premium messaging feature with subscription management

-- User subscriptions table
CREATE TABLE user_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    subscription_type ENUM('monthly', 'semi_annual', 'annual') NOT NULL,
    status ENUM('active', 'cancelled', 'expired', 'pending') DEFAULT 'pending',
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    payment_method ENUM('authorize_net', 'paypal') NOT NULL,
    payment_id VARCHAR(255), -- Transaction ID from payment processor
    amount DECIMAL(10,2) NOT NULL,
    auto_renew BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status),
    INDEX idx_end_date (end_date)
);

-- Conversations table (1-on-1 conversations between users)
CREATE TABLE conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_message_at TIMESTAMP NULL,
    is_archived BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_conversation (LEAST(user1_id, user2_id), GREATEST(user1_id, user2_id)),
    INDEX idx_users (user1_id, user2_id),
    INDEX idx_last_message (last_message_at DESC)
);

-- Messages table
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_text TEXT NOT NULL,
    message_type ENUM('text', 'image', 'file') DEFAULT 'text',
    file_path VARCHAR(500) NULL, -- For image/file messages
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_conversation_created (conversation_id, created_at DESC),
    INDEX idx_sender (sender_id),
    INDEX idx_read_status (is_read, read_at),
    FULLTEXT KEY ft_message_text (message_text)
);

-- User online status for instant messaging
CREATE TABLE user_online_status (
    user_id INT PRIMARY KEY,
    is_online BOOLEAN DEFAULT FALSE,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status_message VARCHAR(255) NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_online (is_online, last_seen)
);

-- Message notifications
CREATE TABLE message_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    message_id INT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, is_read),
    INDEX idx_created (created_at DESC)
);

-- Blocked users (for messaging)
CREATE TABLE blocked_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    blocker_id INT NOT NULL,
    blocked_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(255) NULL,
    FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_block (blocker_id, blocked_id),
    INDEX idx_blocker (blocker_id),
    INDEX idx_blocked (blocked_id)
);

-- Payment transactions log
CREATE TABLE payment_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    subscription_id INT NULL,
    transaction_id VARCHAR(255) NOT NULL,
    payment_method ENUM('authorize_net', 'paypal') NOT NULL,
    transaction_type ENUM('subscription', 'renewal', 'refund') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    gateway_response TEXT NULL, -- JSON response from payment gateway
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id) ON DELETE SET NULL,
    INDEX idx_user_status (user_id, status),
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_created (created_at DESC)
);

-- Subscription pricing plans
CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plan_name VARCHAR(100) NOT NULL,
    plan_type ENUM('monthly', 'semi_annual', 'annual') NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    description TEXT NULL,
    features JSON NULL, -- Store plan features as JSON
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_plan_type (plan_type)
);

-- Insert default subscription plans
INSERT INTO subscription_plans (plan_name, plan_type, price, description, features) VALUES
('Monthly Premium', 'monthly', 9.99, 'Monthly premium messaging access', '["unlimited_messages", "read_receipts", "online_status", "file_sharing"]'),
('6-Month Premium', 'semi_annual', 29.99, '6 months of premium messaging (50% savings)', '["unlimited_messages", "read_receipts", "online_status", "file_sharing", "priority_support"]'),
('Annual Premium', 'annual', 59.99, 'Full year of premium messaging (best value)', '["unlimited_messages", "read_receipts", "online_status", "file_sharing", "priority_support", "exclusive_features"]');

-- Add messaging permission to existing users table (if not exists)
-- ALTER TABLE users ADD COLUMN has_premium_messaging BOOLEAN DEFAULT FALSE;
-- ALTER TABLE users ADD COLUMN premium_expires_at TIMESTAMP NULL;

-- Indexes for performance optimization
CREATE INDEX idx_user_premium ON users(has_premium_messaging, premium_expires_at);
CREATE INDEX idx_conversation_updated ON conversations(updated_at DESC);
CREATE INDEX idx_message_unread ON messages(conversation_id, is_read, created_at);

-- Views for common queries
CREATE VIEW active_subscriptions AS
SELECT 
    us.*,
    u.username,
    u.email,
    sp.plan_name,
    sp.features
FROM user_subscriptions us
JOIN users u ON us.user_id = u.id
JOIN subscription_plans sp ON us.subscription_type = sp.plan_type
WHERE us.status = 'active' AND us.end_date > NOW();

CREATE VIEW conversation_list AS
SELECT 
    c.*,
    u1.username as user1_username,
    u2.username as user2_username,
    m.message_text as last_message,
    m.created_at as last_message_time,
    m.sender_id as last_sender_id,
    (SELECT COUNT(*) FROM messages WHERE conversation_id = c.id AND is_read = FALSE) as unread_count
FROM conversations c
LEFT JOIN users u1 ON c.user1_id = u1.id
LEFT JOIN users u2 ON c.user2_id = u2.id
LEFT JOIN messages m ON c.id = m.conversation_id AND m.created_at = c.last_message_at
WHERE c.is_archived = FALSE
ORDER BY c.last_message_at DESC;
