-- 1sixty8 Shop Admin Schema
-- pos_admin database

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Admin Users (platform operators)
CREATE TABLE admin_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role VARCHAR(20) NOT NULL DEFAULT 'admin'
        CHECK (role IN ('super_admin', 'admin', 'support', 'billing')),
    status VARCHAR(20) DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'locked')),
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Managed Tenants (master record for each store)
CREATE TABLE managed_tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    owner_name VARCHAR(255),
    owner_email VARCHAR(255) NOT NULL,
    owner_phone VARCHAR(50),
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(20),
    timezone VARCHAR(50) DEFAULT 'America/New_York',
    db_host VARCHAR(255) DEFAULT '127.0.0.1',
    db_port VARCHAR(10) DEFAULT '5433',
    db_name VARCHAR(100) NOT NULL UNIQUE,
    db_user VARCHAR(100) NOT NULL,
    db_pass VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'provisioning'
        CHECK (status IN ('provisioning', 'active', 'suspended', 'trial', 'cancelled', 'failed')),
    plan VARCHAR(50) DEFAULT 'standard',
    trial_ends_at DATE,
    billing_email VARCHAR(255),
    schema_version VARCHAR(50),
    provisioned_at TIMESTAMPTZ,
    created_by UUID REFERENCES admin_users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Billing Records
CREATE TABLE billing_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES managed_tenants(id),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'overdue', 'waived', 'refunded')),
    invoice_number VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Support Tickets
CREATE TABLE support_tickets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES managed_tenants(id),
    submitted_by_name VARCHAR(255),
    submitted_by_email VARCHAR(255),
    subject VARCHAR(500) NOT NULL,
    description TEXT,
    priority VARCHAR(20) DEFAULT 'normal'
        CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    status VARCHAR(20) DEFAULT 'open'
        CHECK (status IN ('open', 'in_progress', 'waiting', 'resolved', 'closed')),
    assigned_to UUID REFERENCES admin_users(id),
    resolved_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Support Ticket Replies
CREATE TABLE support_ticket_replies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id UUID NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE,
    author_type VARCHAR(20) NOT NULL CHECK (author_type IN ('admin', 'tenant')),
    author_name VARCHAR(255),
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Usage Stats
CREATE TABLE usage_stats (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES managed_tenants(id),
    recorded_at TIMESTAMPTZ DEFAULT NOW(),
    metric VARCHAR(50) NOT NULL,
    value DECIMAL(12,2) NOT NULL,
    details JSONB DEFAULT '{}'
);
CREATE INDEX idx_usage_stats_tenant ON usage_stats(tenant_id);
CREATE INDEX idx_usage_stats_date ON usage_stats(recorded_at DESC);

-- Admin Audit Log
CREATE TABLE admin_audit_log (
    id BIGSERIAL PRIMARY KEY,
    admin_user_id UUID REFERENCES admin_users(id),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id UUID,
    details JSONB DEFAULT '{}',
    ip_address VARCHAR(45),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Schema migrations tracker
CREATE TABLE schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    description TEXT,
    applied_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO schema_migrations (version, description) VALUES ('001', 'Initial admin schema');
