michangarrito/database/schemas/03-public.sql

-- =============================================================================
-- MICHANGARRITO - 03 PUBLIC (Tenants)
-- =============================================================================
-- Tabla principal de tenants (multi-tenant)
-- =============================================================================

-- Tenants (negocios)
CREATE TABLE IF NOT EXISTS public.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificación
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    business_type VARCHAR(50) NOT NULL,

    -- Contacto
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10),

    -- Configuración
    timezone VARCHAR(50) DEFAULT 'America/Mexico_City',
    currency VARCHAR(3) DEFAULT 'MXN',
    tax_rate DECIMAL(5,2) DEFAULT 16.00,
    tax_included BOOLEAN DEFAULT true,

    -- WhatsApp
    whatsapp_number VARCHAR(20),
    whatsapp_verified BOOLEAN DEFAULT false,
    uses_platform_number BOOLEAN DEFAULT true,

    -- Suscripción
    current_plan_id UUID,
    subscription_status VARCHAR(20) DEFAULT 'trial',

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    onboarding_completed BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tenants_slug ON public.tenants(slug);
CREATE INDEX idx_tenants_phone ON public.tenants(phone);
CREATE INDEX idx_tenants_status ON public.tenants(status);

-- Trigger de updated_at
CREATE TRIGGER update_tenants_updated_at
    BEFORE UPDATE ON public.tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Configuración de tenant
CREATE TABLE IF NOT EXISTS public.tenant_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Horarios
    opening_hour TIME DEFAULT '08:00',
    closing_hour TIME DEFAULT '22:00',
    working_days INTEGER[] DEFAULT ARRAY[1,2,3,4,5,6],

    -- Tickets
    ticket_header TEXT,
    ticket_footer TEXT DEFAULT 'Gracias por su compra',
    print_logo BOOLEAN DEFAULT false,

    -- Notificaciones
    daily_summary_enabled BOOLEAN DEFAULT true,
    daily_summary_time TIME DEFAULT '21:00',
    low_stock_alerts BOOLEAN DEFAULT true,

    -- Fiados
    fiados_enabled BOOLEAN DEFAULT true,
    default_fiado_limit DECIMAL(10,2) DEFAULT 500.00,
    fiado_reminder_days INTEGER DEFAULT 7,

    -- Pedidos
    delivery_enabled BOOLEAN DEFAULT false,
    delivery_fee DECIMAL(10,2) DEFAULT 0.00,
    delivery_radius_km DECIMAL(5,2),

    -- Métodos de pago
    payment_cash BOOLEAN DEFAULT true,
    payment_card_mercadopago BOOLEAN DEFAULT false,
    payment_card_clip BOOLEAN DEFAULT false,
    payment_codi BOOLEAN DEFAULT false,
    payment_transfer BOOLEAN DEFAULT false,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

CREATE TRIGGER update_tenant_configs_updated_at
    BEFORE UPDATE ON public.tenant_configs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();