-- Create scm_tasks table for task tracking under trans schema
-- Run this script to set up the database schema
-- Create trans schema
CREATE SCHEMA IF NOT EXISTS trans;
-- Create table
CREATE TABLE IF NOT EXISTS trans.scm_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL,
    assigned_to UUID NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'not_started',
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    address TEXT,
    scheduled_at TIMESTAMP,
    started_at BIGINT,
    completed_at BIGINT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    CONSTRAINT chk_status CHECK (
        status IN ('not_started', 'in_progress', 'completed')
    )
);
-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_scm_tasks_assigned_date ON trans.scm_tasks (assigned_to, scheduled_at);
CREATE INDEX IF NOT EXISTS idx_scm_tasks_merchant_status ON trans.scm_tasks (merchant_id, status);
CREATE INDEX IF NOT EXISTS idx_scm_tasks_status_scheduled ON trans.scm_tasks (status, scheduled_at);
-- Verify table creation
SELECT column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'trans'
    AND table_name = 'scm_tasks'
ORDER BY ordinal_position;