-- Add purchase system for tracks and albums
-- This allows users to buy individual tracks/albums without subscription

-- Purchases table
CREATE TABLE IF NOT EXISTS purchases (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    purchase_type VARCHAR(50) NOT NULL CHECK (purchase_type IN ('track', 'album')),
    item_id UUID NOT NULL, -- track_id or album_id
    price_fcfa INTEGER NOT NULL,
    currency VARCHAR(10) DEFAULT 'XOF',
    payment_method VARCHAR(100),
    payment_reference VARCHAR(255),
    status VARCHAR(50) DEFAULT 'completed' CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add pricing fields to tracks and albums
ALTER TABLE tracks 
ADD COLUMN IF NOT EXISTS price_fcfa INTEGER DEFAULT 500,
ADD COLUMN IF NOT EXISTS is_purchasable BOOLEAN DEFAULT TRUE;

ALTER TABLE albums 
ADD COLUMN IF NOT EXISTS price_fcfa INTEGER DEFAULT 3000,
ADD COLUMN IF NOT EXISTS is_purchasable BOOLEAN DEFAULT TRUE;

-- User owned content (purchased tracks and albums)
CREATE TABLE IF NOT EXISTS user_owned_content (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content_type VARCHAR(50) NOT NULL CHECK (content_type IN ('track', 'album')),
    content_id UUID NOT NULL,
    purchase_id UUID REFERENCES purchases(id) ON DELETE SET NULL,
    acquired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, content_type, content_id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_purchases_user ON purchases(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_purchases_item ON purchases(purchase_type, item_id);
CREATE INDEX IF NOT EXISTS idx_user_owned_content_user ON user_owned_content(user_id);
CREATE INDEX IF NOT EXISTS idx_user_owned_content_content ON user_owned_content(content_type, content_id);

-- Update payment_transactions to link with purchases
ALTER TABLE payment_transactions
ADD COLUMN IF NOT EXISTS purchase_id UUID REFERENCES purchases(id) ON DELETE SET NULL;

COMMENT ON TABLE purchases IS 'Individual track/album purchases';
COMMENT ON TABLE user_owned_content IS 'Tracks and albums owned by users through purchase';