1
CREATE TABLE IF NOT EXISTS files (
2
    id TEXT PRIMARY KEY,
3
    value TEXT,
4
    metadata TEXT NOT NULL,
5
    file_name TEXT,
6
    file_type TEXT,
7
    file_size TEXT,
8
    upload_ip TEXT,
9
    upload_address TEXT,
10
    list_type TEXT,
11
    timestamp INTEGER,
12
    label TEXT,
13
    directory TEXT,
14
    channel TEXT,
15
    channel_name TEXT,
16
    tg_file_id TEXT,
17
    tg_chat_id TEXT,
18
    tg_bot_token TEXT,
19
    is_chunked BOOLEAN DEFAULT FALSE,
20
    tags TEXT,
21
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
22
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
23
);
24

25
CREATE TABLE IF NOT EXISTS settings (
26
    key TEXT PRIMARY KEY,
27
    value TEXT NOT NULL,
28
    category TEXT,
29
    description TEXT,
30
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
31
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
32
);
33

34
CREATE TABLE IF NOT EXISTS index_operations (
35
    id TEXT PRIMARY KEY,
36
    type TEXT NOT NULL,
37
    timestamp INTEGER NOT NULL,
38
    data TEXT NOT NULL,
39
    processed BOOLEAN DEFAULT FALSE,
40
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
41
);
42

43
CREATE TABLE IF NOT EXISTS index_metadata (
44
    key TEXT PRIMARY KEY,
45
    last_updated INTEGER,
46
    total_count INTEGER DEFAULT 0,
47
    last_operation_id TEXT,
48
    chunk_count INTEGER DEFAULT 0,
49
    chunk_size INTEGER DEFAULT 0,
50
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
51
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
52
);
53

54
CREATE TABLE IF NOT EXISTS other_data (
55
    key TEXT PRIMARY KEY,
56
    value TEXT NOT NULL,
57
    type TEXT,
58
    description TEXT,
59
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
60
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
61
);
62

63
CREATE INDEX IF NOT EXISTS idx_files_timestamp ON files(timestamp DESC);
64
CREATE INDEX IF NOT EXISTS idx_files_directory ON files(directory);
65
CREATE INDEX IF NOT EXISTS idx_files_channel ON files(channel);
66
CREATE INDEX IF NOT EXISTS idx_files_file_type ON files(file_type);
67
CREATE INDEX IF NOT EXISTS idx_files_upload_ip ON files(upload_ip);
68
CREATE INDEX IF NOT EXISTS idx_files_created_at ON files(created_at DESC);
69
CREATE INDEX IF NOT EXISTS idx_files_tags ON files(tags);
70

71
CREATE INDEX IF NOT EXISTS idx_settings_category ON settings(category);
72

73
CREATE INDEX IF NOT EXISTS idx_index_operations_timestamp ON index_operations(timestamp);
74
CREATE INDEX IF NOT EXISTS idx_index_operations_processed ON index_operations(processed);
75
CREATE INDEX IF NOT EXISTS idx_index_operations_type ON index_operations(type);
76

77
CREATE INDEX IF NOT EXISTS idx_other_data_type ON other_data(type);
78

79
CREATE TRIGGER IF NOT EXISTS update_files_updated_at
80
    AFTER UPDATE ON files
81
    BEGIN
82
        UPDATE files SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
83
    END;
84

85
CREATE TRIGGER IF NOT EXISTS update_settings_updated_at
86
    AFTER UPDATE ON settings
87
    BEGIN
88
        UPDATE settings SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key;
89
    END;
90

91
CREATE TRIGGER IF NOT EXISTS update_index_metadata_updated_at
92
    AFTER UPDATE ON index_metadata
93
    BEGIN
94
        UPDATE index_metadata SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key;
95
    END;
96

97
CREATE TRIGGER IF NOT EXISTS update_other_data_updated_at
98
    AFTER UPDATE ON other_data
99
    BEGIN
100
        UPDATE other_data SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key;
101
    END;