-- Migration: Add authority prices and price override tracking
-- Date: 2025-11-15
-- Description: Add tables for authority-controlled pricing and price override auditing

-- Authority prices table (for price-controlled items)
CREATE TABLE IF NOT EXISTS authority_prices (
    authority_price_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    barcode text NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    item_name text,
    category text,
    authority_source text DEFAULT 'Government Authority',
    effective_date timestamp DEFAULT NOW(),
    isActive BOOLEAN NOT NULL DEFAULT TRUE,
    updatedAt timestamptz NOT NULL DEFAULT now(),
    
    UNIQUE(barcode, isActive) -- Only one active price per barcode
);

-- Index for fast barcode lookups
CREATE INDEX IF NOT EXISTS idx_authority_prices_barcode ON authority_prices(barcode);
CREATE INDEX IF NOT EXISTS idx_authority_prices_active ON authority_prices(isActive);

-- Price overrides audit table
CREATE TABLE IF NOT EXISTS price_overrides (
    override_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    barcode text NOT NULL,
    authority_price NUMERIC(10,2) NOT NULL,
    override_price NUMERIC(10,2) NOT NULL,
    user_id uuid,
    reason text,
    created_at timestamptz NOT NULL DEFAULT now(),
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Index for audit queries
CREATE INDEX IF NOT EXISTS idx_price_overrides_barcode ON price_overrides(barcode);
CREATE INDEX IF NOT EXISTS idx_price_overrides_user ON price_overrides(user_id);
CREATE INDEX IF NOT EXISTS idx_price_overrides_date ON price_overrides(created_at);

-- Add some sample authority prices (these would come from government/authority data)
INSERT INTO authority_prices (barcode, price, item_name, category, authority_source) VALUES
('1234567890123', 2.50, 'Milk 1L', 'Dairy', 'Food Standards Authority'),
('2345678901234', 1.75, 'Bread White', 'Bakery', 'Food Standards Authority'),
('3456789012345', 3.00, 'Rice 1kg', 'Grains', 'Food Standards Authority'),
('4567890123456', 0.50, 'Sugar 500g', 'Sweeteners', 'Food Standards Authority'),
('5678901234567', 4.25, 'Cooking Oil 500ml', 'Oils', 'Food Standards Authority')
ON CONFLICT (barcode, isActive) DO NOTHING;

-- Comments
COMMENT ON TABLE authority_prices IS 'Government or authority controlled prices for specific products';
COMMENT ON TABLE price_overrides IS 'Audit log of when users override authority-controlled prices';
COMMENT ON COLUMN authority_prices.barcode IS 'Product barcode - any format accepted';
COMMENT ON COLUMN authority_prices.price IS 'Official price set by authorities';
COMMENT ON COLUMN authority_prices.authority_source IS 'Which authority/agency set this price';
COMMENT ON COLUMN price_overrides.authority_price IS 'The official price that was overridden';
COMMENT ON COLUMN price_overrides.override_price IS 'The price the user actually set';