-- name: InitializeSchema :exec
CREATE TABLE IF NOT EXISTS content (
    id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    html_content TEXT NOT NULL,
    original_template TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    updated_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    last_edited_by TEXT DEFAULT 'system' NOT NULL,
    PRIMARY KEY (id, site_id)
);

-- name: InitializeVersionsTable :exec
CREATE TABLE IF NOT EXISTS content_versions (
    version_id INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    html_content TEXT NOT NULL,
    original_template TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    created_by TEXT DEFAULT 'system' NOT NULL
);

-- name: InitializeCollectionsTable :exec
CREATE TABLE IF NOT EXISTS collections (
    id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    container_html TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    updated_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    last_edited_by TEXT DEFAULT 'system' NOT NULL,
    PRIMARY KEY (id, site_id)
);

-- name: InitializeCollectionTemplatesTable :exec
CREATE TABLE IF NOT EXISTS collection_templates (
    template_id INTEGER PRIMARY KEY AUTOINCREMENT,
    collection_id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    name TEXT NOT NULL,
    html_template TEXT NOT NULL,
    is_default INTEGER DEFAULT 0 NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    FOREIGN KEY (collection_id, site_id) REFERENCES collections(id, site_id) ON DELETE CASCADE
);

-- name: InitializeCollectionItemsTable :exec
CREATE TABLE IF NOT EXISTS collection_items (
    item_id TEXT NOT NULL,
    collection_id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    template_id INTEGER NOT NULL,
    html_content TEXT NOT NULL,
    position INTEGER NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    updated_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    last_edited_by TEXT DEFAULT 'system' NOT NULL,
    PRIMARY KEY (item_id, collection_id, site_id),
    FOREIGN KEY (collection_id, site_id) REFERENCES collections(id, site_id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES collection_templates(template_id) ON DELETE RESTRICT
);

-- name: InitializeCollectionItemVersionsTable :exec
CREATE TABLE IF NOT EXISTS collection_item_versions (
    version_id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id TEXT NOT NULL,
    collection_id TEXT NOT NULL,
    site_id TEXT NOT NULL,
    html_content TEXT NOT NULL,
    template_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    created_by TEXT DEFAULT 'system' NOT NULL,
    FOREIGN KEY (item_id, collection_id, site_id) REFERENCES collection_items(item_id, collection_id, site_id) ON DELETE CASCADE
);

-- name: CreateContentSiteIndex :exec
CREATE INDEX IF NOT EXISTS idx_content_site_id ON content(site_id);

-- name: CreateContentUpdatedAtIndex :exec
CREATE INDEX IF NOT EXISTS idx_content_updated_at ON content(updated_at);

-- name: CreateVersionsLookupIndex :exec
CREATE INDEX IF NOT EXISTS idx_content_versions_lookup ON content_versions(content_id, site_id, created_at DESC);

-- name: CreateCollectionsSiteIndex :exec
CREATE INDEX IF NOT EXISTS idx_collections_site_id ON collections(site_id);

-- name: CreateCollectionsUpdatedAtIndex :exec
CREATE INDEX IF NOT EXISTS idx_collections_updated_at ON collections(updated_at);

-- name: CreateCollectionTemplatesLookupIndex :exec
CREATE INDEX IF NOT EXISTS idx_collection_templates_lookup ON collection_templates(collection_id, site_id);

-- name: CreateCollectionTemplatesDefaultIndex :exec
CREATE INDEX IF NOT EXISTS idx_collection_templates_default ON collection_templates(collection_id, site_id, is_default DESC);

-- name: CreateCollectionItemsLookupIndex :exec
CREATE INDEX IF NOT EXISTS idx_collection_items_lookup ON collection_items(collection_id, site_id, position);

-- name: CreateCollectionItemsTemplateIndex :exec
CREATE INDEX IF NOT EXISTS idx_collection_items_template ON collection_items(template_id);

-- name: CreateCollectionItemVersionsLookupIndex :exec
CREATE INDEX IF NOT EXISTS idx_collection_item_versions_lookup ON collection_item_versions(item_id, collection_id, site_id, created_at DESC);

-- name: CreateCollectionTemplatesOneDefaultIndex :exec
CREATE UNIQUE INDEX IF NOT EXISTS idx_collection_templates_one_default 
ON collection_templates(collection_id, site_id) 
WHERE is_default = 1;

-- name: CreateUpdateTrigger :exec
CREATE TRIGGER IF NOT EXISTS update_content_updated_at
AFTER UPDATE ON content
FOR EACH ROW
BEGIN
    UPDATE content SET updated_at = strftime('%s', 'now') WHERE id = NEW.id AND site_id = NEW.site_id;
END;

-- name: CreateCollectionsUpdateTrigger :exec
CREATE TRIGGER IF NOT EXISTS update_collections_updated_at
AFTER UPDATE ON collections
FOR EACH ROW
BEGIN
    UPDATE collections SET updated_at = strftime('%s', 'now') WHERE id = NEW.id AND site_id = NEW.site_id;
END;

-- name: CreateCollectionItemsUpdateTrigger :exec
CREATE TRIGGER IF NOT EXISTS update_collection_items_updated_at
AFTER UPDATE ON collection_items
FOR EACH ROW
BEGIN
    UPDATE collection_items SET updated_at = strftime('%s', 'now') WHERE item_id = NEW.item_id AND collection_id = NEW.collection_id AND site_id = NEW.site_id;
END;