acmcc/supabase/migrations/20260315213410_415e81a2-a1a9-472f-a6b5-a33a5aca869f.sql

-- Associations table
CREATE TABLE public.associations (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT,
  city TEXT,
  state TEXT,
  zip TEXT,
  phone TEXT,
  email TEXT,
  management_fee NUMERIC(10,2),
  fiscal_year_start INTEGER DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Units table
CREATE TABLE public.units (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  unit_number TEXT NOT NULL,
  address TEXT,
  city TEXT,
  state TEXT,
  zip TEXT,
  bedrooms INTEGER,
  bathrooms NUMERIC(3,1),
  sqft INTEGER,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'delinquent')),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Owners table
CREATE TABLE public.owners (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  unit_id UUID REFERENCES public.units(id) ON DELETE SET NULL,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  mailing_address TEXT,
  is_primary BOOLEAN DEFAULT false,
  is_tenant BOOLEAN DEFAULT false,
  move_in_date DATE,
  move_out_date DATE,
  balance NUMERIC(12,2) DEFAULT 0,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Violations table
CREATE TABLE public.violations (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  unit_id UUID REFERENCES public.units(id) ON DELETE SET NULL,
  owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  description TEXT,
  category TEXT,
  status TEXT DEFAULT 'open' CHECK (status IN ('open', 'pending', 'resolved', 'escalated', 'closed')),
  priority TEXT DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
  due_date DATE,
  resolved_date DATE,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- ARC Applications table
CREATE TABLE public.arc_applications (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  unit_id UUID REFERENCES public.units(id) ON DELETE SET NULL,
  owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  description TEXT,
  project_type TEXT,
  estimated_cost NUMERIC(12,2),
  status TEXT DEFAULT 'submitted' CHECK (status IN ('submitted', 'under_review', 'approved', 'denied', 'withdrawn')),
  submitted_date DATE DEFAULT CURRENT_DATE,
  review_date DATE,
  decision_notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.associations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.units ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.owners ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.violations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.arc_applications ENABLE ROW LEVEL SECURITY;

-- RLS Policies (authenticated users can read all, admin/manager can write)
CREATE POLICY "Authenticated users can view associations" ON public.associations FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage associations" ON public.associations FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Managers can manage associations" ON public.associations FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'manager'));

CREATE POLICY "Authenticated users can view units" ON public.units FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage units" ON public.units FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Managers can manage units" ON public.units FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'manager'));

CREATE POLICY "Authenticated users can view owners" ON public.owners FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage owners" ON public.owners FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Managers can manage owners" ON public.owners FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'manager'));

CREATE POLICY "Authenticated users can view violations" ON public.violations FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage violations" ON public.violations FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Managers can manage violations" ON public.violations FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'manager'));

CREATE POLICY "Authenticated users can view arc_applications" ON public.arc_applications FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage arc_applications" ON public.arc_applications FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Managers can manage arc_applications" ON public.arc_applications FOR ALL TO authenticated USING (public.has_role(auth.uid(), 'manager'));

-- Indexes
CREATE INDEX idx_units_association ON public.units(association_id);
CREATE INDEX idx_owners_association ON public.owners(association_id);
CREATE INDEX idx_owners_unit ON public.owners(unit_id);
CREATE INDEX idx_violations_association ON public.violations(association_id);
CREATE INDEX idx_violations_unit ON public.violations(unit_id);
CREATE INDEX idx_arc_applications_association ON public.arc_applications(association_id);

-- Updated_at triggers
CREATE TRIGGER update_associations_updated_at BEFORE UPDATE ON public.associations FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER update_units_updated_at BEFORE UPDATE ON public.units FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER update_owners_updated_at BEFORE UPDATE ON public.owners FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER update_violations_updated_at BEFORE UPDATE ON public.violations FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER update_arc_applications_updated_at BEFORE UPDATE ON public.arc_applications FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();