-- Update user_profiles to support 'business' role
ALTER TABLE public.user_profiles 
ADD CONSTRAINT user_profiles_role_check 
CHECK (role = ANY (ARRAY['homeowner'::text, 'realtor'::text, 'business'::text, 'admin'::text]));


-- Link auth users to business profiles
CREATE TABLE public.business_users (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    business_id uuid NOT NULL,
    auth_id uuid NOT NULL,
    role text NOT NULL DEFAULT 'admin' CHECK (role = ANY (ARRAY['admin'::text, 'manager'::text, 'employee'::text])),
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT business_users_pkey PRIMARY KEY (id),
    CONSTRAINT business_users_business_id_fkey FOREIGN KEY (business_id) REFERENCES public.businesses(id) ON DELETE CASCADE,
    CONSTRAINT business_users_auth_id_fkey FOREIGN KEY (auth_id) REFERENCES auth.users(id) ON DELETE CASCADE,
    CONSTRAINT business_users_unique_business_auth UNIQUE (business_id, auth_id)
);

-- Add indexes
CREATE INDEX idx_business_users_business_id ON public.business_users(business_id);
CREATE INDEX idx_business_users_auth_id ON public.business_users(auth_id);


-- Employee profiles within businesses
CREATE TABLE public.business_employees (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    business_id uuid NOT NULL,
    auth_id uuid,
    employee_number text,
    first_name text NOT NULL,
    last_name text NOT NULL,
    email text NOT NULL,
    phone text,
    hourly_rate numeric,
    is_active boolean NOT NULL DEFAULT true,
    max_hours_per_week integer DEFAULT 40,
    emergency_contact jsonb,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT business_employees_pkey PRIMARY KEY (id),
    CONSTRAINT business_employees_business_id_fkey FOREIGN KEY (business_id) REFERENCES public.businesses(id) ON DELETE CASCADE,
    CONSTRAINT business_employees_auth_id_fkey FOREIGN KEY (auth_id) REFERENCES auth.users(id) ON DELETE SET NULL,
    CONSTRAINT business_employees_unique_business_email UNIQUE (business_id, email)
);

-- Employee specializations/skills
CREATE TABLE public.employee_specializations (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    employee_id uuid NOT NULL,
    specialization_name text NOT NULL,
    category text, -- 'skill', 'certification', 'experience'
    proficiency_level integer DEFAULT 1 CHECK (proficiency_level >= 1 AND proficiency_level <= 5),
    years_experience integer,
    is_primary boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT employee_specializations_pkey PRIMARY KEY (id),
    CONSTRAINT employee_specializations_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES public.business_employees(id) ON DELETE CASCADE,
    CONSTRAINT employee_specializations_unique_emp_spec UNIQUE (employee_id, specialization_name)
);

-- Employee schedules/calendar
CREATE TABLE public.employee_schedules (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    employee_id uuid NOT NULL,
    date date NOT NULL,
    start_time time,
    end_time time,
    schedule_type text NOT NULL DEFAULT 'work' CHECK (schedule_type = ANY (ARRAY['work'::text, 'vacation'::text, 'sick'::text, 'training'::text])),
    notes text,
    is_available boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT employee_schedules_pkey PRIMARY KEY (id),
    CONSTRAINT employee_schedules_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES public.business_employees(id) ON DELETE CASCADE,
    CONSTRAINT employee_schedules_unique_emp_date UNIQUE (employee_id, date)
);

-- Add indexes
CREATE INDEX idx_business_employees_business_id ON public.business_employees(business_id);
CREATE INDEX idx_employee_specializations_employee_id ON public.employee_specializations(employee_id);
CREATE INDEX idx_employee_schedules_employee_id ON public.employee_schedules(employee_id);
CREATE INDEX idx_employee_schedules_date ON public.employee_schedules(date);


-- Business projects
CREATE TABLE public.business_projects (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    business_id uuid NOT NULL,
    project_number text,
    title text NOT NULL,
    description text,
    client_name text NOT NULL,
    client_email text,
    client_phone text,
    client_address text,
    client_postal_code text,
    client_city text,
    project_type text NOT NULL, -- 'maintenance', 'repair', 'construction', 'inspection'
    status text NOT NULL DEFAULT 'planned' CHECK (status = ANY (ARRAY['planned'::text, 'in_progress'::text, 'on_hold'::text, 'completed'::text, 'cancelled'::text])),
    priority text DEFAULT 'medium' CHECK (priority = ANY (ARRAY['low'::text, 'medium'::text, 'high'::text, 'urgent'::text])),
    estimated_hours numeric,
    actual_hours numeric DEFAULT 0,
    estimated_cost numeric,
    actual_cost numeric DEFAULT 0,
    start_date date,
    end_date date,
    completion_date date,
    assigned_employees jsonb, -- Array of employee IDs
    project_manager_id uuid,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT business_projects_pkey PRIMARY KEY (id),
    CONSTRAINT business_projects_business_id_fkey FOREIGN KEY (business_id) REFERENCES public.businesses(id) ON DELETE CASCADE,
    CONSTRAINT business_projects_project_manager_id_fkey FOREIGN KEY (project_manager_id) REFERENCES public.business_employees(id)
);

-- Project photos
CREATE TABLE public.project_photos (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    project_id uuid NOT NULL,
    file_path text NOT NULL,
    file_name text NOT NULL,
    file_size integer,
    mime_type text,
    taken_at timestamp with time zone,
    taken_by uuid, -- Employee who took the photo
    photo_type text DEFAULT 'progress' CHECK (photo_type = ANY (ARRAY['before'::text, 'during'::text, 'after'::text, 'progress'::text, 'issue'::text])),
    description text,
    is_before_work boolean DEFAULT false,
    is_after_work boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT project_photos_pkey PRIMARY KEY (id),
    CONSTRAINT project_photos_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.business_projects(id) ON DELETE CASCADE,
    CONSTRAINT project_photos_taken_by_fkey FOREIGN KEY (taken_by) REFERENCES public.business_employees(id)
);

-- Work logs/time tracking
CREATE TABLE public.work_logs (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    project_id uuid NOT NULL,
    employee_id uuid NOT NULL,
    log_date date NOT NULL,
    start_time time NOT NULL,
    end_time time,
    hours_worked numeric,
    work_description text NOT NULL,
    work_type text DEFAULT 'labor' CHECK (work_type = ANY (ARRAY['labor'::text, 'travel'::text, 'waiting'::text, 'admin'::text])),
    is_billable boolean DEFAULT true,
    billable_rate numeric,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT work_logs_pkey PRIMARY KEY (id),
    CONSTRAINT work_logs_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.business_projects(id) ON DELETE CASCADE,
    CONSTRAINT work_logs_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES public.business_employees(id) ON DELETE CASCADE
);

-- Add indexes
CREATE INDEX idx_business_projects_business_id ON public.business_projects(business_id);
CREATE INDEX idx_business_projects_status ON public.business_projects(status);
CREATE INDEX idx_business_projects_start_date ON public.business_projects(start_date);
CREATE INDEX idx_project_photos_project_id ON public.project_photos(project_id);
CREATE INDEX idx_work_logs_project_id ON public.work_logs(project_id);
CREATE INDEX idx_work_logs_employee_id ON public.work_logs(employee_id);
CREATE INDEX idx_work_logs_log_date ON public.work_logs(log_date);


-- Material pricing management
CREATE TABLE public.material_pricing (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    business_id uuid NOT NULL,
    material_name text NOT NULL,
    category text, -- 'paint', 'wood', 'metal', 'plumbing', 'electrical', etc.
    unit text DEFAULT 'piece' CHECK (unit = ANY (ARRAY['piece'::text, 'meter'::text, 'square_meter'::text, 'liter'::text, 'kg'::text, 'hour'::text])),
    cost_price numeric NOT NULL,
    selling_price numeric NOT NULL,
    markup_percentage numeric GENERATED ALWAYS AS (ROUND(((selling_price - cost_price) / cost_price) * 100, 2)) STORED,
    supplier_name text,
    supplier_contact text,
    min_order_quantity numeric DEFAULT 1,
    current_stock numeric DEFAULT 0,
    low_stock_threshold numeric DEFAULT 0,
    is_active boolean DEFAULT true,
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT material_pricing_pkey PRIMARY KEY (id),
    CONSTRAINT material_pricing_business_id_fkey FOREIGN KEY (business_id) REFERENCES public.businesses(id) ON DELETE CASCADE,
    CONSTRAINT material_pricing_unique_business_material UNIQUE (business_id, material_name)
);

-- Business quotes/documents for AI context
CREATE TABLE public.business_quotes (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    business_id uuid NOT NULL,
    file_name text NOT NULL,
    file_path text NOT NULL,
    file_size integer,
    mime_type text,
    document_type text DEFAULT 'quote' CHECK (document_type = ANY (ARRAY['quote'::text, 'invoice'::text, 'contract'::text, 'estimate'::text, 'report'::text])),
    project_type text, -- 'painting', 'flooring', 'plumbing', etc.
    client_name text,
    quote_amount numeric,
    valid_until date,
    status text DEFAULT 'active' CHECK (status = ANY (ARRAY['active'::text, 'expired'::text, 'used'::text, 'cancelled'::text])),
    ai_processed boolean DEFAULT false,
    ai_context_extracted jsonb,
    uploaded_by uuid,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    
    CONSTRAINT business_quotes_pkey PRIMARY KEY (id),
    CONSTRAINT business_quotes_business_id_fkey FOREIGN KEY (business_id) REFERENCES public.businesses(id) ON DELETE CASCADE,
    CONSTRAINT business_quotes_uploaded_by_fkey FOREIGN KEY (uploaded_by) REFERENCES public.business_employees(id)
);

-- Add indexes
CREATE INDEX idx_material_pricing_business_id ON public.material_pricing(business_id);
CREATE INDEX idx_business_quotes_business_id ON public.business_quotes(business_id);
CREATE INDEX idx_business_quotes_status ON public.business_quotes(status);


-- Enable RLS on all new tables
ALTER TABLE public.business_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.business_employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.employee_specializations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.employee_schedules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.business_projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project_photos ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.work_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.material_pricing ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.business_quotes ENABLE ROW LEVEL SECURITY;

-- Basic RLS policies (you can customize these based on your needs)
CREATE POLICY "Business users can view their own business data" ON public.business_users
    FOR SELECT USING (auth_id = auth.uid());

CREATE POLICY "Business employees are viewable by business users" ON public.business_employees
    FOR SELECT USING (
        business_id IN (
            SELECT business_id FROM public.business_users 
            WHERE auth_id = auth.uid()
        )
    );


    