-- ============================================================
-- Bestincome.com - Micro Task Marketplace
-- PostgreSQL Schema (Phase 1: Core - Users, Auth, Tasks, Wallet)
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- USERS
-- ============================================================
CREATE TYPE user_role AS ENUM ('worker', 'employer', 'admin');
CREATE TYPE user_status AS ENUM ('active', 'suspended', 'banned', 'pending');

CREATE TABLE users (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email               VARCHAR(255) UNIQUE NOT NULL,
    password_hash       VARCHAR(255) NOT NULL,
    full_name           VARCHAR(150) NOT NULL,
    username            VARCHAR(50) UNIQUE NOT NULL,
    role                user_role NOT NULL DEFAULT 'worker',
    status              user_status NOT NULL DEFAULT 'pending',
    avatar_url          TEXT,
    country             VARCHAR(100),
    phone               VARCHAR(30),

    -- email verification
    email_verified      BOOLEAN NOT NULL DEFAULT FALSE,
    email_verify_token  VARCHAR(255),
    email_verify_expires TIMESTAMPTZ,

    -- password reset
    reset_token         VARCHAR(255),
    reset_token_expires TIMESTAMPTZ,

    -- 2FA
    two_fa_enabled      BOOLEAN NOT NULL DEFAULT FALSE,
    two_fa_secret       VARCHAR(255),

    -- google oauth
    google_id           VARCHAR(255) UNIQUE,

    -- kyc
    kyc_status          VARCHAR(20) NOT NULL DEFAULT 'not_submitted', -- not_submitted | pending | approved | rejected
    kyc_document_url    TEXT,

    -- referral
    referral_code       VARCHAR(20) UNIQUE,
    referred_by         UUID REFERENCES users(id),

    -- gamification
    level               INT NOT NULL DEFAULT 1,
    xp_points           INT NOT NULL DEFAULT 0,

    last_login_at       TIMESTAMPTZ,
    last_login_ip       VARCHAR(45),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_referral_code ON users(referral_code);

-- Device / session login history (security: device login history)
CREATE TABLE login_history (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ip_address  VARCHAR(45),
    device      VARCHAR(255),
    browser     VARCHAR(100),
    os          VARCHAR(100),
    location    VARCHAR(255),
    success     BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_login_history_user ON login_history(user_id);

-- Refresh tokens (JWT rotation)
CREATE TABLE refresh_tokens (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  VARCHAR(255) NOT NULL,
    expires_at  TIMESTAMPTZ NOT NULL,
    revoked     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);

-- ============================================================
-- WALLET SYSTEM
-- ============================================================
CREATE TABLE wallets (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id             UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    deposit_balance     NUMERIC(14,4) NOT NULL DEFAULT 0,   -- employer funds to spend
    earnings_balance    NUMERIC(14,4) NOT NULL DEFAULT 0,   -- worker earnings, withdrawable
    escrow_balance      NUMERIC(14,4) NOT NULL DEFAULT 0,   -- funds locked in active tasks
    referral_balance    NUMERIC(14,4) NOT NULL DEFAULT 0,
    currency            VARCHAR(10) NOT NULL DEFAULT 'USD',
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TYPE transaction_type AS ENUM (
    'deposit', 'withdrawal', 'task_payment', 'task_earning',
    'refund', 'commission', 'referral_bonus', 'daily_bonus', 'adjustment'
);
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');

CREATE TABLE transactions (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            transaction_type NOT NULL,
    status          transaction_status NOT NULL DEFAULT 'pending',
    amount          NUMERIC(14,4) NOT NULL,
    fee             NUMERIC(14,4) NOT NULL DEFAULT 0,
    currency        VARCHAR(10) NOT NULL DEFAULT 'USD',
    payment_method  VARCHAR(50), -- stripe | paypal | crypto | bank | manual
    reference_id    VARCHAR(255), -- external gateway transaction id
    description     TEXT,
    metadata        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_transactions_user ON transactions(user_id);
CREATE INDEX idx_transactions_type ON transactions(type);

CREATE TABLE withdrawal_requests (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount          NUMERIC(14,4) NOT NULL,
    method          VARCHAR(50) NOT NULL, -- bank | paypal | crypto | mobile_banking
    account_details JSONB NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending | approved | rejected | paid
    admin_note      TEXT,
    processed_by    UUID REFERENCES users(id),
    processed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_withdrawals_user ON withdrawal_requests(user_id);
CREATE INDEX idx_withdrawals_status ON withdrawal_requests(status);

-- ============================================================
-- TASKS (MICRO JOBS)
-- ============================================================
CREATE TYPE task_category AS ENUM (
    'facebook_like','facebook_follow','facebook_share','facebook_comment',
    'instagram_follow','instagram_like','tiktok_follow','tiktok_like',
    'youtube_subscribe','youtube_watch','app_install','website_visit',
    'survey','review','data_entry','signup','telegram_join',
    'discord_join','twitter_follow','reddit_upvote','custom'
);
CREATE TYPE task_status AS ENUM ('draft','active','paused','completed','deleted');
CREATE TYPE proof_type AS ENUM ('screenshot','file','quiz','text','link');

CREATE TABLE tasks (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    employer_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title               VARCHAR(255) NOT NULL,
    description         TEXT NOT NULL,
    category            task_category NOT NULL,
    instructions        TEXT NOT NULL,
    target_url          TEXT,

    reward_per_task     NUMERIC(10,4) NOT NULL,
    worker_limit        INT NOT NULL,
    workers_completed    INT NOT NULL DEFAULT 0,

    -- targeting
    target_countries    TEXT[],           -- e.g. {'US','GB','BD'}
    target_devices      TEXT[],           -- e.g. {'mobile','desktop','tablet'}
    target_browsers     TEXT[],           -- e.g. {'chrome','firefox','safari'}

    proof_type          proof_type NOT NULL DEFAULT 'screenshot',
    quiz_questions      JSONB,            -- if proof_type = quiz

    auto_approve_minutes INT,             -- null = manual only
    status              task_status NOT NULL DEFAULT 'active',
    is_featured         BOOLEAN NOT NULL DEFAULT FALSE,
    featured_until      TIMESTAMPTZ,

    total_budget        NUMERIC(14,4) NOT NULL,   -- reward_per_task * worker_limit (escrowed)

    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_tasks_employer ON tasks(employer_id);
CREATE INDEX idx_tasks_category ON tasks(category);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_featured ON tasks(is_featured);

CREATE TYPE submission_status AS ENUM ('pending','approved','rejected','auto_approved');

CREATE TABLE task_submissions (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    worker_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    proof_text      TEXT,
    proof_files     TEXT[],          -- cloudinary/s3 urls
    quiz_answers    JSONB,
    status          submission_status NOT NULL DEFAULT 'pending',
    reject_reason   TEXT,
    screenshot_hash VARCHAR(64),     -- for duplicate detection
    reviewed_by     UUID REFERENCES users(id),
    reviewed_at     TIMESTAMPTZ,
    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(task_id, worker_id)
);
CREATE INDEX idx_submissions_task ON task_submissions(task_id);
CREATE INDEX idx_submissions_worker ON task_submissions(worker_id);
CREATE INDEX idx_submissions_status ON task_submissions(status);
CREATE INDEX idx_submissions_hash ON task_submissions(screenshot_hash);

CREATE TABLE favorite_tasks (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    task_id     UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, task_id)
);

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE notifications (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       VARCHAR(255) NOT NULL,
    message     TEXT NOT NULL,
    type        VARCHAR(50) NOT NULL DEFAULT 'general', -- task | payment | system | kyc
    link        TEXT,
    is_read     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_notifications_user ON notifications(user_id, is_read);

-- ============================================================
-- updated_at auto-touch trigger
-- ============================================================
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
CREATE TRIGGER trg_tasks_updated_at BEFORE UPDATE ON tasks
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();