-- Vestigium Database Schema
-- SQLite 3

-- Objects table
CREATE TABLE IF NOT EXISTS objects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    short_description TEXT,
    long_description_md TEXT,
    price_amount INTEGER NOT NULL, -- in smallest currency unit (grosze for PLN, cents for EUR)
    currency TEXT NOT NULL DEFAULT 'PLN',
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'available', 'reserved', 'accepted', 'archived')),
    primary_image_path TEXT,
    gallery_json TEXT, -- JSON array of image paths
    stripe_product_id TEXT,
    stripe_price_id TEXT,
    stripe_checkout_session_id TEXT,
    stripe_payment_intent_id TEXT,
    reserved_until DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Content blocks table
CREATE TABLE IF NOT EXISTS content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT NOT NULL UNIQUE,
    value_md TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Inquiries/Process submissions table
CREATE TABLE IF NOT EXISTS inquiries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    message TEXT,
    status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'handled')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Login attempts for rate limiting
CREATE TABLE IF NOT EXISTS login_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ip_address TEXT NOT NULL,
    username TEXT,
    success INTEGER DEFAULT 0,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Webhook logs for debugging
CREATE TABLE IF NOT EXISTS webhook_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id TEXT,
    event_type TEXT,
    payload_json TEXT,
    processed INTEGER DEFAULT 0,
    error_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- CSRF tokens table
CREATE TABLE IF NOT EXISTS csrf_tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_objects_status ON objects(status);
CREATE INDEX IF NOT EXISTS idx_objects_slug ON objects(slug);
CREATE INDEX IF NOT EXISTS idx_inquiries_status ON inquiries(status);
CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(ip_address, attempted_at);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_event ON webhook_logs(event_id);
CREATE INDEX IF NOT EXISTS idx_csrf_tokens_token ON csrf_tokens(token);

-- Insert default content blocks
INSERT OR IGNORE INTO content (key, value_md) VALUES
    ('homepage_short_text_md', 'Objects forged through intention, bound to their keeper. Each piece carries the weight of careful creation — an artifact designed to accompany, not to adorn.'),
    ('homepage_quote', '"The object does not rush its effect."'),
    ('amulet_long_text_md', '# The Nature of Vestigium Objects

These are not ornaments. They are not fashion. They are artifacts — each one singular, each one created through a process that cannot be hurried.

A Vestigium object enters your life at the moment it should. Not before. Not after.

## The Process

Each object begins with intention. Yours and ours. The materials are selected not for their commercial value, but for their resonance. The creation takes as long as it takes.

When it is ready, you will know.

## What You Receive

A singular object. Documentation of its creation. A discrete carrying vessel. Our commitment to its integrity.

The object arrives when it arrives.'),
    ('footer_tagline', 'Vestigium. Objects that do not rush their effect.'),
    ('legal_content_md', '# Legal Information

## Privacy Policy

We collect only the information necessary to fulfill your request. Your data is never sold or shared with third parties for marketing purposes.

## Terms of Service

By initiating the process or acquiring an object, you agree to these terms. All sales are final. Each object is unique and cannot be returned or exchanged.

## Contact

For inquiries, use the process form on this website.

---

*Content to be provided by site owner.*');

-- Trigger to update updated_at on objects
CREATE TRIGGER IF NOT EXISTS update_objects_timestamp
    AFTER UPDATE ON objects
    FOR EACH ROW
BEGIN
    UPDATE objects SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

-- Trigger to update updated_at on content
CREATE TRIGGER IF NOT EXISTS update_content_timestamp
    AFTER UPDATE ON content
    FOR EACH ROW
BEGIN
    UPDATE content SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;
