-- Core tables for AI-Native CMS

CREATE TABLE IF NOT EXISTS pages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER DEFAULT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT,
    status TEXT DEFAULT 'draft', -- 'draft', 'published', 'scheduled'
    published_at DATETIME,
    author_id INTEGER,
    template TEXT DEFAULT 'standard', -- 'standard', 'blank'
    grapes_data TEXT, -- JSON storage for visual builder
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT,
    status TEXT DEFAULT 'draft', -- 'draft', 'published', 'scheduled'
    published_at DATETIME,
    author_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    filepath TEXT NOT NULL,
    mimetype TEXT NOT NULL,
    filesize INTEGER,
    alt_text TEXT,
    uploaded_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS taxonomies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL -- e.g., 'category', 'tag'
);

CREATE TABLE IF NOT EXISTS term_relationships (
    object_id INTEGER NOT NULL, -- page or post id
    taxonomy_id INTEGER NOT NULL,
    object_type TEXT NOT NULL, -- 'page' or 'post'
    PRIMARY KEY (object_id, taxonomy_id, object_type)
);

CREATE TABLE IF NOT EXISTS meta (
    object_id INTEGER NOT NULL,
    object_type TEXT NOT NULL, -- 'page', 'post', 'media', 'taxonomy'
    meta_key TEXT NOT NULL,
    meta_value TEXT,
    PRIMARY KEY (object_id, object_type, meta_key)
);

CREATE TABLE IF NOT EXISTS redirects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_path TEXT NOT NULL UNIQUE,
    to_path TEXT NOT NULL,
    type TEXT DEFAULT '301', -- '301', '302'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    role TEXT DEFAULT 'author', -- 'admin', 'author'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages (slug);
CREATE INDEX IF NOT EXISTS idx_posts_slug ON posts (slug);
CREATE INDEX IF NOT EXISTS idx_taxonomies_slug ON taxonomies (slug);
CREATE INDEX IF NOT EXISTS idx_meta_object ON meta (object_id, object_type);
CREATE INDEX IF NOT EXISTS idx_term_relationships_object ON term_relationships (object_id, object_type);
CREATE INDEX IF NOT EXISTS idx_term_relationships_taxonomy ON term_relationships (taxonomy_id);
CREATE INDEX IF NOT EXISTS idx_redirects_from_path ON redirects (from_path);
CREATE INDEX IF NOT EXISTS idx_users_username ON users (username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);

CREATE TABLE IF NOT EXISTS revisions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    object_id INTEGER NOT NULL,
    object_type TEXT NOT NULL, -- 'page' or 'post'
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
);

CREATE TABLE IF NOT EXISTS custom_post_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    singular TEXT NOT NULL,
    icon TEXT DEFAULT 'fas fa-file',
    fields TEXT, -- JSON array of custom fields
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS custom_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_type TEXT NOT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    content TEXT,
    fields TEXT, -- JSON object of custom field values
    status TEXT DEFAULT 'draft',
    author_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_custom_posts_type ON custom_posts (post_type);
CREATE INDEX IF NOT EXISTS idx_custom_posts_slug ON custom_posts (slug);
