-- ============================================
-- TEMPLATE-SAAS: Tenants Table
-- Schema: tenants
-- Version: 1.0.0
-- ============================================

CREATE TABLE tenants.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Basic info
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255) UNIQUE,
    logo_url VARCHAR(500),

    -- Status
    status tenants.tenant_status DEFAULT 'pending' NOT NULL,

    -- Subscription
    plan_id UUID,  -- FK to plans.plans
    subscription_status tenants.subscription_status DEFAULT 'trialing',
    trial_ends_at TIMESTAMPTZ,
    subscription_ends_at TIMESTAMPTZ,

    -- Stripe integration
    stripe_customer_id VARCHAR(255) UNIQUE,
    stripe_subscription_id VARCHAR(255) UNIQUE,

    -- Settings (JSONB for flexibility)
    settings JSONB DEFAULT '{}'::jsonb,
    -- Example settings:
    -- {
    --   "timezone": "America/Mexico_City",
    --   "locale": "es-MX",
    --   "currency": "MXN",
    --   "date_format": "DD/MM/YYYY",
    --   "features": {}
    -- }

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Audit fields
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID,
    updated_by UUID,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT valid_slug CHECK (slug ~ '^[a-z0-9]([a-z0-9-]*[a-z0-9])?$'),
    CONSTRAINT slug_length CHECK (LENGTH(slug) >= 3 AND LENGTH(slug) <= 50)
);

-- Indexes
CREATE INDEX idx_tenants_status ON tenants.tenants(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_slug ON tenants.tenants(slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_stripe_customer ON tenants.tenants(stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;
CREATE INDEX idx_tenants_plan ON tenants.tenants(plan_id) WHERE deleted_at IS NULL;

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION tenants.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_tenants_updated_at
    BEFORE UPDATE ON tenants.tenants
    FOR EACH ROW
    EXECUTE FUNCTION tenants.update_updated_at();

-- Comments
COMMENT ON TABLE tenants.tenants IS 'Core tenant table for multi-tenancy';
COMMENT ON COLUMN tenants.tenants.slug IS 'URL-safe identifier, used in subdomains';
COMMENT ON COLUMN tenants.tenants.settings IS 'Tenant-specific configuration (JSON)';
COMMENT ON COLUMN tenants.tenants.stripe_customer_id IS 'Stripe Customer ID for billing';