-- Multi-tenant POS System Database Schema
-- This schema supports row-level security with tenant_id for multi-tenancy

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enums
CREATE TYPE user_role AS ENUM ('super_admin', 'manager', 'staff');
CREATE TYPE subscription_status AS ENUM ('active', 'inactive', 'cancelled', 'trial', 'suspended');
CREATE TYPE subscription_plan AS ENUM ('basic', 'premium', 'enterprise');
CREATE TYPE payment_method AS ENUM ('cash', 'card', 'digital_wallet', 'bank_transfer');
CREATE TYPE sale_status AS ENUM ('completed', 'pending', 'cancelled', 'refunded');
CREATE TYPE expense_category AS ENUM ('supplies', 'utilities', 'rent', 'marketing', 'equipment', 'salaries', 'other');

-- Tenants table (businesses)
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    business_name VARCHAR(255) NOT NULL,
    business_email VARCHAR(255) UNIQUE NOT NULL,
    business_phone VARCHAR(20),
    business_address TEXT,
    plan subscription_plan NOT NULL DEFAULT 'basic',
    subscription_status subscription_status NOT NULL DEFAULT 'trial',
    stripe_customer_id VARCHAR(255),
    stripe_subscription_id VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE NULL
);

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role user_role NOT NULL,
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP WITH TIME ZONE,
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE NULL
);

-- Products table
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sku VARCHAR(100) NOT NULL,
    barcode VARCHAR(255),
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    cost DECIMAL(10,2) NOT NULL CHECK (cost >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    low_stock_threshold INTEGER DEFAULT 5,
    category VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE NULL,
    UNIQUE(tenant_id, sku)
);

-- Sales table
CREATE TABLE sales (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    sale_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    payment_method payment_method NOT NULL,
    status sale_status NOT NULL DEFAULT 'completed',
    notes TEXT,
    processed_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(tenant_id, sale_number)
);

-- Sale items table
CREATE TABLE sale_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sale_id UUID NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    product_name VARCHAR(255) NOT NULL, -- Store snapshot for historical data
    product_sku VARCHAR(100) NOT NULL,  -- Store snapshot for historical data
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    total_price DECIMAL(10,2) NOT NULL CHECK (total_price >= 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Expenses table
CREATE TABLE expenses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    category expense_category NOT NULL,
    description TEXT NOT NULL,
    receipt_url VARCHAR(500),
    expense_date DATE NOT NULL,
    recorded_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE NULL
);

-- Inventory adjustments table for tracking stock changes
CREATE TABLE inventory_adjustments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    adjustment_type VARCHAR(20) NOT NULL CHECK (adjustment_type IN ('sale', 'restock', 'adjustment', 'damage')),
    quantity_change INTEGER NOT NULL, -- positive for increase, negative for decrease
    previous_stock INTEGER NOT NULL,
    new_stock INTEGER NOT NULL,
    reason TEXT,
    reference_id UUID, -- Could reference sale_id or other transaction
    adjusted_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Subscription plans table (for pricing tiers)
CREATE TABLE subscription_plans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    plan_type subscription_plan NOT NULL,
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    max_products INTEGER,
    max_users INTEGER,
    features JSONB NOT NULL DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Webhook events table for Stripe integration
CREATE TABLE webhook_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stripe_event_id VARCHAR(255) UNIQUE NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    processed BOOLEAN DEFAULT false,
    tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
    payload JSONB NOT NULL,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    processed_at TIMESTAMP WITH TIME ZONE
);

-- Create indexes for performance optimization
-- Tenant-scoped indexes for multi-tenant queries
CREATE INDEX idx_users_tenant_id ON users(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_role ON users(role);

CREATE INDEX idx_products_tenant_id ON products(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_sku ON products(tenant_id, sku) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_barcode ON products(barcode) WHERE barcode IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_products_category ON products(tenant_id, category) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_low_stock ON products(tenant_id) WHERE stock <= low_stock_threshold AND deleted_at IS NULL;

CREATE INDEX idx_sales_tenant_id ON sales(tenant_id);
CREATE INDEX idx_sales_created_at ON sales(tenant_id, created_at DESC);
CREATE INDEX idx_sales_status ON sales(tenant_id, status);
CREATE INDEX idx_sales_payment_method ON sales(tenant_id, payment_method);
CREATE INDEX idx_sales_number ON sales(tenant_id, sale_number);

CREATE INDEX idx_sale_items_sale_id ON sale_items(sale_id);
CREATE INDEX idx_sale_items_product_id ON sale_items(product_id);

CREATE INDEX idx_expenses_tenant_id ON expenses(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_expenses_date ON expenses(tenant_id, expense_date DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_expenses_category ON expenses(tenant_id, category) WHERE deleted_at IS NULL;

CREATE INDEX idx_inventory_adjustments_tenant_id ON inventory_adjustments(tenant_id);
CREATE INDEX idx_inventory_adjustments_product_id ON inventory_adjustments(product_id);
CREATE INDEX idx_inventory_adjustments_created_at ON inventory_adjustments(tenant_id, created_at DESC);

-- Tenants table indexes
CREATE INDEX idx_tenants_business_email ON tenants(business_email) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_subscription_status ON tenants(subscription_status) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_stripe_customer_id ON tenants(stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;

-- Webhook events indexes
CREATE INDEX idx_webhook_events_stripe_id ON webhook_events(stripe_event_id);
CREATE INDEX idx_webhook_events_processed ON webhook_events(processed, created_at);
CREATE INDEX idx_webhook_events_tenant_id ON webhook_events(tenant_id) WHERE tenant_id IS NOT NULL;

-- Functions for automatic updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Triggers for updated_at
CREATE TRIGGER update_tenants_updated_at BEFORE UPDATE ON tenants FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_sales_updated_at BEFORE UPDATE ON sales FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_expenses_updated_at BEFORE UPDATE ON expenses FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to generate sale numbers
CREATE OR REPLACE FUNCTION generate_sale_number(tenant_uuid UUID)
RETURNS VARCHAR AS $$
DECLARE
    sale_count INTEGER;
    sale_number VARCHAR;
BEGIN
    -- Get the count of sales for this tenant today
    SELECT COUNT(*) + 1 INTO sale_count
    FROM sales
    WHERE tenant_id = tenant_uuid
    AND DATE(created_at) = CURRENT_DATE;
    
    -- Generate sale number: YYYYMMDD-NNNN
    sale_number := TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(sale_count::TEXT, 4, '0');
    
    RETURN sale_number;
END;
$$ LANGUAGE plpgsql;

-- Row Level Security (RLS) setup for multi-tenancy
-- This ensures users can only access data from their own tenant

-- Enable RLS on tenant-scoped tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE sale_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE expenses ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_adjustments ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
-- Note: These policies assume you'll set the current tenant_id in your application context
-- For example, using SET LOCAL app.current_tenant = 'tenant-uuid';

-- Users policies
CREATE POLICY tenant_users_policy ON users
    FOR ALL
    TO authenticated_users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Products policies
CREATE POLICY tenant_products_policy ON products
    FOR ALL
    TO authenticated_users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Sales policies
CREATE POLICY tenant_sales_policy ON sales
    FOR ALL
    TO authenticated_users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Sale items policies (inherit from sales)
CREATE POLICY tenant_sale_items_policy ON sale_items
    FOR ALL
    TO authenticated_users
    USING (EXISTS (
        SELECT 1 FROM sales s 
        WHERE s.id = sale_items.sale_id 
        AND s.tenant_id = current_setting('app.current_tenant')::UUID
    ));

-- Expenses policies
CREATE POLICY tenant_expenses_policy ON expenses
    FOR ALL
    TO authenticated_users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Inventory adjustments policies
CREATE POLICY tenant_inventory_adjustments_policy ON inventory_adjustments
    FOR ALL
    TO authenticated_users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Create roles
CREATE ROLE authenticated_users;
CREATE ROLE super_admin_users;

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated_users;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO authenticated_users;
GRANT ALL ON ALL TABLES IN SCHEMA public TO super_admin_users;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO super_admin_users;

-- Insert default subscription plans
INSERT INTO subscription_plans (name, plan_type, price_monthly, price_yearly, max_products, max_users, features) VALUES
('Basic Plan', 'basic', 29.99, 299.99, 100, 3, '{"reports": false, "advanced_inventory": false, "api_access": false}'),
('Premium Plan', 'premium', 79.99, 799.99, 1000, 10, '{"reports": true, "advanced_inventory": true, "api_access": false}'),
('Enterprise Plan', 'enterprise', 199.99, 1999.99, -1, -1, '{"reports": true, "advanced_inventory": true, "api_access": true, "custom_integrations": true}');

-- Sample data for development (optional)
-- INSERT INTO tenants (business_name, business_email, plan) VALUES 
-- ('Demo Store', 'demo@example.com', 'basic');