43 lines
1.4 KiB
MySQL
43 lines
1.4 KiB
MySQL
|
-- 076_create_gear_items.sql
|
||
|
|
-- Gear/Equipment inventory (Phase 2 - Coming Soon features)
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS public.gear_items (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
||
|
|
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
category VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
brand VARCHAR(200) NOT NULL DEFAULT '',
|
||
|
|
model VARCHAR(200) NOT NULL DEFAULT '',
|
||
|
|
serial_number VARCHAR(100),
|
||
|
|
image VARCHAR(500),
|
||
|
|
images JSONB DEFAULT '[]',
|
||
|
|
|
||
|
|
status VARCHAR(50) NOT NULL DEFAULT 'Active',
|
||
|
|
condition VARCHAR(50) NOT NULL DEFAULT 'Good',
|
||
|
|
|
||
|
|
purchase_date DATE,
|
||
|
|
purchase_price DECIMAL(12, 2) DEFAULT 0,
|
||
|
|
currency VARCHAR(3) DEFAULT 'USD',
|
||
|
|
vendor VARCHAR(200),
|
||
|
|
order_number VARCHAR(100),
|
||
|
|
|
||
|
|
warranty_expire DATE,
|
||
|
|
warranty_type VARCHAR(50),
|
||
|
|
support_contact VARCHAR(200),
|
||
|
|
|
||
|
|
specs JSONB DEFAULT '{}',
|
||
|
|
notes TEXT,
|
||
|
|
documents JSONB DEFAULT '[]',
|
||
|
|
maintenance_history JSONB DEFAULT '[]',
|
||
|
|
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_gear_items_user_id ON public.gear_items(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_gear_items_category ON public.gear_items(category);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_gear_items_status ON public.gear_items(status);
|
||
|
|
|
||
|
|
COMMENT ON TABLE public.gear_items IS 'User equipment/gear inventory for studio management';
|