-- Migration: Add hiring fee, thumbs rating, and portfolio tables
-- Run against production database

-- Part A: Hiring fee fields on contracts
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS hiring_fee_paid BOOLEAN DEFAULT FALSE;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS hiring_fee_amount NUMERIC(10,2) DEFAULT 50.00;

-- Part C: Thumbs up/down fields on ratings
ALTER TABLE ratings ADD COLUMN IF NOT EXISTS is_positive BOOLEAN;
ALTER TABLE ratings ADD COLUMN IF NOT EXISTS rating_version INTEGER DEFAULT 1;
ALTER TABLE ratings ALTER COLUMN overall_score DROP NOT NULL;

-- Part E: Portfolio items table
CREATE TABLE IF NOT EXISTS portfolio_items (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    worker_id INTEGER NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    description TEXT,
    image_url VARCHAR(500) NOT NULL,
    trade_category VARCHAR(100),
    contract_id INTEGER REFERENCES contracts(id),
    display_order INTEGER DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_portfolio_worker ON portfolio_items(worker_id);

-- Update alembic version
INSERT INTO alembic_version (version_num) VALUES ('db47ac1fe2a1')
ON CONFLICT (version_num) DO NOTHING;
