-- Add cost fields to orders table
ALTER TABLE orders ADD COLUMN total_cost DECIMAL(10,2) DEFAULT 0.00 CHECK (total_cost >= 0);

-- Add cost fields to order_items table
ALTER TABLE order_items ADD COLUMN unit_cost DECIMAL(10,2) DEFAULT 0.00 CHECK (unit_cost >= 0);
ALTER TABLE order_items ADD COLUMN total_cost DECIMAL(10,2) DEFAULT 0.00 CHECK (total_cost >= 0);

-- Add boolean flags for void and refund instead of status enums
ALTER TABLE orders ADD COLUMN is_void BOOLEAN DEFAULT FALSE;
ALTER TABLE orders ADD COLUMN is_refund BOOLEAN DEFAULT FALSE;
ALTER TABLE orders ADD COLUMN void_reason VARCHAR(255);
ALTER TABLE orders ADD COLUMN voided_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE orders ADD COLUMN voided_by UUID REFERENCES users(id);
ALTER TABLE orders ADD COLUMN refund_reason VARCHAR(255);
ALTER TABLE orders ADD COLUMN refunded_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE orders ADD COLUMN refunded_by UUID REFERENCES users(id);

-- Add new payment statuses (keep this for payment tracking)
ALTER TABLE orders DROP CONSTRAINT orders_payment_status_check;
ALTER TABLE orders ADD CONSTRAINT orders_payment_status_check CHECK (payment_status IN ('pending', 'completed', 'failed', 'refunded', 'partially_refunded'));

-- Add refund_amount field to orders
ALTER TABLE orders ADD COLUMN refund_amount DECIMAL(10,2) DEFAULT 0.00 CHECK (refund_amount >= 0);

-- Add sequence number tracking for order numbers
CREATE TABLE order_sequences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    outlet_id UUID NOT NULL REFERENCES outlets(id) ON DELETE CASCADE,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    sequence_number INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(organization_id, outlet_id, year, month)
);

-- Indexes for order_sequences
CREATE INDEX idx_order_sequences_org_outlet ON order_sequences(organization_id, outlet_id);
CREATE INDEX idx_order_sequences_year_month ON order_sequences(year, month);

-- Indexes for the new boolean flags
CREATE INDEX idx_orders_is_void ON orders(is_void);
CREATE INDEX idx_orders_is_refund ON orders(is_refund);
