-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "postgis";  -- For geolocation

-- Users table (ciudadanos)
CREATE TABLE usuarios (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE NOT NULL,
  nombres TEXT NOT NULL,
  apellidos TEXT NOT NULL,
  cedula TEXT UNIQUE NOT NULL CHECK (length(cedula) = 10),
  parroquia TEXT NOT NULL,
  barrio TEXT NOT NULL,
  tipo TEXT NOT NULL DEFAULT 'ciudadano' CHECK (tipo IN ('ciudadano', 'administrador')),
  activo BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Administrators table
CREATE TABLE administradores (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE NOT NULL,
  nombres TEXT NOT NULL,
  apellidos TEXT NOT NULL,
  cedula TEXT UNIQUE NOT NULL CHECK (length(cedula) = 10),
  activo BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- News table
CREATE TABLE noticias (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  titulo TEXT NOT NULL,
  contenido TEXT NOT NULL,
  resumen TEXT,
  imagen_url TEXT,
  categoria TEXT NOT NULL,
  autor_id UUID REFERENCES administradores(id),
  publicada BOOLEAN DEFAULT false,
  fecha_publicacion TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Surveys table
CREATE TABLE encuestas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  titulo TEXT NOT NULL,
  descripcion TEXT NOT NULL,
  tipo TEXT NOT NULL CHECK (tipo IN ('opcion_multiple', 'abierta', 'calificacion')),
  opciones JSONB,
  fecha_inicio TIMESTAMPTZ NOT NULL,
  fecha_fin TIMESTAMPTZ NOT NULL,
  activa BOOLEAN DEFAULT true,
  parroquia_destino TEXT,
  barrio_destino TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Survey responses table
CREATE TABLE respuestas_encuestas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  encuesta_id UUID REFERENCES encuestas(id) ON DELETE CASCADE,
  usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
  respuesta JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(encuesta_id, usuario_id)  -- One response per user per survey
);

-- Reports table with PostGIS geometry
CREATE TABLE reportes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
  categoria TEXT NOT NULL CHECK (categoria IN (
    'alumbrado', 'baches', 'limpieza', 'agua', 'alcantarillado',
    'parques', 'señalizacion', 'seguridad', 'ruido', 'otro'
  )),
  descripcion TEXT NOT NULL,
  ubicacion GEOGRAPHY(POINT, 4326) NOT NULL,  -- PostGIS for location
  direccion TEXT,
  fotos TEXT[],  -- Array of image URLs
  estado TEXT NOT NULL DEFAULT 'pendiente' CHECK (estado IN (
    'pendiente', 'en_revision', 'en_proceso', 'resuelto', 'rechazado'
  )),
  prioridad TEXT DEFAULT 'media' CHECK (prioridad IN (
    'baja', 'media', 'alta', 'urgente'
  )),
  notas_admin TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_reportes_usuario ON reportes(usuario_id);
CREATE INDEX idx_reportes_estado ON reportes(estado);
CREATE INDEX idx_reportes_categoria ON reportes(categoria);
CREATE INDEX idx_reportes_ubicacion ON reportes USING GIST(ubicacion);
CREATE INDEX idx_noticias_publicada ON noticias(publicada);
CREATE INDEX idx_encuestas_activa ON encuestas(activa);

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_usuarios_updated_at BEFORE UPDATE ON usuarios
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_administradores_updated_at BEFORE UPDATE ON administradores
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_noticias_updated_at BEFORE UPDATE ON noticias
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_encuestas_updated_at BEFORE UPDATE ON encuestas
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_reportes_updated_at BEFORE UPDATE ON reportes
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Enable RLS on all tables
ALTER TABLE usuarios ENABLE ROW LEVEL SECURITY;
ALTER TABLE administradores ENABLE ROW LEVEL SECURITY;
ALTER TABLE noticias ENABLE ROW LEVEL SECURITY;
ALTER TABLE encuestas ENABLE ROW LEVEL SECURITY;
ALTER TABLE respuestas_encuestas ENABLE ROW LEVEL SECURITY;
ALTER TABLE reportes ENABLE ROW LEVEL SECURITY;

-- USUARIOS: Users can read their own data
CREATE POLICY "Users can view own profile" ON usuarios
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON usuarios
  FOR UPDATE USING (auth.uid() = id);

-- NOTICIAS: Public read, admin write
CREATE POLICY "Anyone can view published news" ON noticias
  FOR SELECT USING (publicada = true);

CREATE POLICY "Admins can manage news" ON noticias
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

-- ENCUESTAS: Users can view active surveys for their location
CREATE POLICY "Users can view active surveys" ON encuestas
  FOR SELECT USING (
    activa = true
    AND NOW() BETWEEN fecha_inicio AND fecha_fin
    AND (
      parroquia_destino IS NULL
      OR parroquia_destino IN (
        SELECT parroquia FROM usuarios WHERE id = auth.uid()
      )
    )
  );

CREATE POLICY "Admins can manage surveys" ON encuestas
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

-- RESPUESTAS_ENCUESTAS: Users can submit and view own responses
CREATE POLICY "Users can create own responses" ON respuestas_encuestas
  FOR INSERT WITH CHECK (auth.uid() = usuario_id);

CREATE POLICY "Users can view own responses" ON respuestas_encuestas
  FOR SELECT USING (auth.uid() = usuario_id);

CREATE POLICY "Admins can view all responses" ON respuestas_encuestas
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

-- REPORTES: Users can manage own reports, admins can view all
CREATE POLICY "Users can create own reports" ON reportes
  FOR INSERT WITH CHECK (auth.uid() = usuario_id);

CREATE POLICY "Users can view own reports" ON reportes
  FOR SELECT USING (auth.uid() = usuario_id);

CREATE POLICY "Users can update own reports" ON reportes
  FOR UPDATE USING (auth.uid() = usuario_id);

CREATE POLICY "Admins can view all reports" ON reportes
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

CREATE POLICY "Admins can update all reports" ON reportes
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

-- Allow authenticated users to upload report photos
CREATE POLICY "Users can upload report photos"
  ON storage.objects FOR INSERT
  TO authenticated
  WITH CHECK (bucket_id = 'reportes-fotos');

-- Allow public read access to report photos
CREATE POLICY "Public can view report photos"
  ON storage.objects FOR SELECT
  TO public
  USING (bucket_id = 'reportes-fotos');

-- Allow admins to upload news images
CREATE POLICY "Admins can upload news images"
  ON storage.objects FOR INSERT
  TO authenticated
  WITH CHECK (
    bucket_id = 'noticias-imagenes'
    AND EXISTS (
      SELECT 1 FROM administradores
      WHERE id = auth.uid() AND activo = true
    )
  );

-- Allow public read access to news images
CREATE POLICY "Public can view news images"
  ON storage.objects FOR SELECT
  TO public
  USING (bucket_id = 'noticias-imagenes');