CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  logo_url TEXT,
  address TEXT,
  phone TEXT,
  email TEXT,
  rfc TEXT,
  razon_social TEXT,
  regimen_fiscal TEXT,
  codigo_postal TEXT,
  pac_provider TEXT CHECK (pac_provider IN ('facturama', 'sw_sapien')),
  pac_api_key TEXT,
  subscription_plan TEXT NOT NULL DEFAULT 'trial' CHECK (subscription_plan IN ('basico', 'pro', 'enterprise', 'trial')),
  subscription_status TEXT NOT NULL DEFAULT 'trial' CHECK (subscription_status IN ('active', 'trial', 'suspended', 'cancelled')),
  trial_ends_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '14 days'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  role TEXT NOT NULL DEFAULT 'receptionist' CHECK (role IN ('owner', 'admin', 'doctor', 'receptionist')),
  specialty TEXT,
  license_number TEXT,
  color TEXT DEFAULT '#3B82F6',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  avatar_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_clinic ON users(clinic_id);

CREATE TABLE branches (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  is_main BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE INDEX idx_branches_clinic ON branches(clinic_id);

CREATE OR REPLACE FUNCTION auth.clinic_id()
RETURNS UUID AS $$
  SELECT clinic_id FROM public.users WHERE id = auth.uid()
$$ LANGUAGE SQL STABLE SECURITY DEFINER;

ALTER TABLE clinics ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own clinic" ON clinics FOR SELECT USING (id = auth.clinic_id());
CREATE POLICY "Owners can update own clinic" ON clinics FOR UPDATE USING (id = auth.clinic_id());

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view clinic members" ON users FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Owners can insert users" ON users FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());
CREATE POLICY "Owners can update users" ON users FOR UPDATE USING (clinic_id = auth.clinic_id());

ALTER TABLE branches ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view clinic branches" ON branches FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Users can manage branches" ON branches FOR ALL USING (clinic_id = auth.clinic_id());