-- UVORA.NET — Complete PostgreSQL Schema
-- Run: psql -U uvora_net_user -d uvora_net -f schema.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- USERS
-- ============================================================

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4() UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    company_name VARCHAR(255),
    phone VARCHAR(50),
    vat_id VARCHAR(50),
    country VARCHAR(2),
    email_verified BOOLEAN DEFAULT false,
    email_verify_token VARCHAR(255),
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active',        -- active, suspended, banned
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE user_addresses (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE NOT NULL,
    label VARCHAR(50) DEFAULT 'billing',         -- billing, shipping
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state_region VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(2) NOT NULL,
    is_default BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE refresh_tokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    user_agent VARCHAR(500),
    ip_address VARCHAR(45),
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE admin_users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    role VARCHAR(30) DEFAULT 'admin',            -- super_admin, admin, support
    active BOOLEAN DEFAULT true,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE admin_refresh_tokens (
    id SERIAL PRIMARY KEY,
    admin_id INTEGER REFERENCES admin_users(id) ON DELETE CASCADE NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- SERVICE CATALOG
-- ============================================================

CREATE TABLE service_categories (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    icon VARCHAR(100),
    sort_order INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true
);

CREATE TABLE services (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    category_id INTEGER REFERENCES service_categories(id) NOT NULL,
    name VARCHAR(255) NOT NULL,
    short_description TEXT,
    full_description TEXT,
    price_cents INTEGER NOT NULL,
    compare_at_price_cents INTEGER,               -- for displayed discounts
    currency VARCHAR(3) DEFAULT 'EUR',
    delivery_time_hours INTEGER NOT NULL,
    delivery_type VARCHAR(20) NOT NULL DEFAULT 'both',   -- file, html, both
    growth_os_service_key VARCHAR(100) NOT NULL,
    brief_schema JSONB NOT NULL DEFAULT '{"fields":[]}',
    thumbnail_url VARCHAR(500),
    active BOOLEAN DEFAULT true,
    featured BOOLEAN DEFAULT false,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE service_addons (
    id SERIAL PRIMARY KEY,
    service_id INTEGER REFERENCES services(id) ON DELETE CASCADE NOT NULL,
    name VARCHAR(255) NOT NULL,
    price_cents INTEGER NOT NULL,
    active BOOLEAN DEFAULT true
);

-- ============================================================
-- ORDERS
-- ============================================================

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(30) UNIQUE NOT NULL,     -- ex: UV-2026-000123
    user_id INTEGER REFERENCES users(id) NOT NULL,
    service_id INTEGER REFERENCES services(id) NOT NULL,
    status VARCHAR(30) DEFAULT 'pending_payment',
    -- pending_payment, paid, processing, delivered, failed, refunded, cancelled
    subtotal_cents INTEGER NOT NULL,
    addons_cents INTEGER DEFAULT 0,
    discount_cents INTEGER DEFAULT 0,
    total_cents INTEGER NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    coupon_code VARCHAR(50),
    growth_os_job_id VARCHAR(255),
    error_message TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_addons (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE NOT NULL,
    addon_id INTEGER REFERENCES service_addons(id) NOT NULL,
    price_cents INTEGER NOT NULL
);

CREATE TABLE order_briefs (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE NOT NULL,
    brief_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_status_history (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE NOT NULL,
    from_status VARCHAR(30),
    to_status VARCHAR(30) NOT NULL,
    changed_by VARCHAR(20) NOT NULL,               -- system, admin, client
    changed_by_id INTEGER,
    note TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE deliveries (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE NOT NULL UNIQUE,
    delivery_type VARCHAR(20) NOT NULL,            -- file, html
    html_content TEXT,
    file_path VARCHAR(500),
    file_original_name VARCHAR(255),
    file_size_bytes INTEGER,
    access_token VARCHAR(255) UNIQUE NOT NULL,
    downloaded_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- INVOICING
-- ============================================================

CREATE TABLE invoices (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(30) UNIQUE NOT NULL,     -- ex: INV-2026-000123
    order_id INTEGER REFERENCES orders(id) NOT NULL,
    user_id INTEGER REFERENCES users(id) NOT NULL,
    subtotal_cents INTEGER NOT NULL,
    tax_cents INTEGER DEFAULT 0,
    tax_rate_percent NUMERIC(5,2) DEFAULT 0,
    total_cents INTEGER NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    billing_details JSONB NOT NULL,                 -- snapshot of name/address/vat at time of issuance
    pdf_path VARCHAR(500),
    status VARCHAR(20) DEFAULT 'issued',            -- issued, paid, void, refunded
    issued_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- PAYMENTS -- MULTI-PROVIDER
-- ============================================================

CREATE TABLE payment_providers_config (
    id SERIAL PRIMARY KEY,
    provider_key VARCHAR(30) UNIQUE NOT NULL,       -- stripe, paypal, mobilpay, euplatesc, revolut
    display_name VARCHAR(100) NOT NULL,
    enabled BOOLEAN DEFAULT false,
    config JSONB NOT NULL DEFAULT '{}',             -- public keys, mode (sandbox/live), no secrets
    sort_order INTEGER DEFAULT 0
);

CREATE TABLE payment_transactions (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4() UNIQUE NOT NULL,
    order_id INTEGER REFERENCES orders(id) NOT NULL,
    user_id INTEGER REFERENCES users(id) NOT NULL,
    provider_key VARCHAR(30) NOT NULL,
    provider_transaction_id VARCHAR(255),
    provider_session_id VARCHAR(255),
    amount_cents INTEGER NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    status VARCHAR(30) DEFAULT 'created',
    -- created, pending, authorized, captured, failed, refunded, partially_refunded
    raw_payload JSONB,
    failure_reason TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE refunds (
    id SERIAL PRIMARY KEY,
    payment_transaction_id INTEGER REFERENCES payment_transactions(id) NOT NULL,
    amount_cents INTEGER NOT NULL,
    reason TEXT,
    status VARCHAR(20) DEFAULT 'pending',           -- pending, completed, failed
    initiated_by_admin_id INTEGER REFERENCES admin_users(id),
    provider_refund_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE webhooks_log (
    id SERIAL PRIMARY KEY,
    source VARCHAR(50) NOT NULL,                    -- stripe, paypal, mobilpay, euplatesc, revolut, growth_os
    event_type VARCHAR(100),
    payload JSONB NOT NULL,
    signature_valid BOOLEAN,
    processed BOOLEAN DEFAULT false,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- SUPPORT TICKETS
-- ============================================================

CREATE TABLE ticket_departments (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    active BOOLEAN DEFAULT true
);

CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    ticket_number VARCHAR(30) UNIQUE NOT NULL,      -- ex: TK-000456
    user_id INTEGER REFERENCES users(id) NOT NULL,
    department_id INTEGER REFERENCES ticket_departments(id) NOT NULL,
    order_id INTEGER REFERENCES orders(id),          -- optional, ticket linked to an order
    subject VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'open',               -- open, answered, client_reply, closed
    priority VARCHAR(20) DEFAULT 'normal',           -- low, normal, high, urgent
    assigned_admin_id INTEGER REFERENCES admin_users(id),
    last_reply_by VARCHAR(20),                       -- client, admin
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    closed_at TIMESTAMP
);

CREATE TABLE ticket_messages (
    id SERIAL PRIMARY KEY,
    ticket_id INTEGER REFERENCES tickets(id) ON DELETE CASCADE NOT NULL,
    author_type VARCHAR(20) NOT NULL,                -- client, admin
    author_id INTEGER NOT NULL,
    message TEXT NOT NULL,
    internal_note BOOLEAN DEFAULT false,             -- visible only to admin
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE ticket_attachments (
    id SERIAL PRIMARY KEY,
    ticket_message_id INTEGER REFERENCES ticket_messages(id) ON DELETE CASCADE NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_original_name VARCHAR(255) NOT NULL,
    file_size_bytes INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- COUPONS
-- ============================================================

CREATE TABLE coupons (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_type VARCHAR(20) NOT NULL,             -- percent, fixed
    discount_value INTEGER NOT NULL,                -- percent or cents
    max_uses INTEGER,
    used_count INTEGER DEFAULT 0,
    valid_from TIMESTAMP,
    valid_until TIMESTAMP,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_services_active ON services(active, category_id);
CREATE INDEX idx_deliveries_token ON deliveries(access_token);
CREATE INDEX idx_payment_tx_order ON payment_transactions(order_id);
CREATE INDEX idx_payment_tx_provider ON payment_transactions(provider_key, provider_transaction_id);
CREATE INDEX idx_tickets_user ON tickets(user_id);
CREATE INDEX idx_tickets_status ON tickets(status);
CREATE INDEX idx_ticket_messages_ticket ON ticket_messages(ticket_id);
CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);
CREATE INDEX idx_invoices_user ON invoices(user_id);

-- ============================================================
-- MINIMAL SEED
-- ============================================================

INSERT INTO service_categories (slug, name, sort_order) VALUES
('web', 'Website & Design', 1),
('marketing', 'Marketing & Ads', 2),
('automation', 'Automation & AI', 3);

INSERT INTO ticket_departments (slug, name) VALUES
('billing', 'Billing & Payments'),
('technical', 'Technical Support'),
('sales', 'Sales & Orders');

INSERT INTO payment_providers_config (provider_key, display_name, enabled, sort_order) VALUES
('stripe', 'Card payment (Stripe)', true, 1),
('paypal', 'PayPal', true, 2),
('revolut', 'Revolut Pay', false, 3),
('mobilpay', 'MobilPay (Netopia)', false, 4),
('euplatesc', 'EuPlatesc', false, 5);