creek

 1import 'package:sqflite/sqflite.dart';
 2import 'package:path/path.dart';
 3
 4class AppDatabase {
 5  static Database? _db;
 6  static const String _dbName = 'database_v7.db';
 7
 8  static Future<Database> get db async {
 9    if (_db != null) return _db!;
10    _db = await _init();
11    return _db!;
12  }
13
14  static Future<Database> _init() async {
15    final path = join(await getDatabasesPath(), _dbName);
16
17    return await openDatabase(
18      path,
19      version: 1,
20      onConfigure: (db) async {
21        await db.execute('PRAGMA foreign_keys = ON');
22      },
23      onCreate: (db, version) async {
24        // 1. PROJECTS
25        await db.execute('''
26          CREATE TABLE projects (
27            id INTEGER PRIMARY KEY AUTOINCREMENT,
28            title TEXT NOT NULL,
29            description TEXT,
30            parent_id INTEGER, 
31            global_stylesheet TEXT,
32            last_accessed_at TEXT,
33            assets_path TEXT DEFAULT '[]',
34            created_at TEXT,
35            FOREIGN KEY (parent_id) REFERENCES projects (id) ON DELETE CASCADE
36          )
37        ''');
38
39        // Create Inbox Project (ID 0) for Drafts
40        await db.rawInsert(
41          '''
42          INSERT INTO projects (id, title, description, last_accessed_at, created_at)
43          VALUES (0, 'Inbox', 'Holding area for shared images', ?, ?)
44        ''',
45          [DateTime.now().toIso8601String(), DateTime.now().toIso8601String()],
46        );
47
48        // 2. IMAGES (Moodboard)
49        await db.execute('''
50          CREATE TABLE images (
51            id TEXT PRIMARY KEY,
52            project_id INTEGER NOT NULL,
53            file_path TEXT NOT NULL,
54            name TEXT,
55            tags TEXT DEFAULT '[]', 
56            analysis_data TEXT,
57            created_at TEXT,
58            status TEXT DEFAULT 'pending',
59            FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
60          )
61        ''');
62
63        // 3. FILES (Project Work)
64        await db.execute('''
65          CREATE TABLE files (
66            id TEXT PRIMARY KEY,
67            project_id INTEGER NOT NULL,
68            file_path TEXT NOT NULL,
69            name TEXT,
70            description TEXT,
71            tags TEXT DEFAULT '[]',
72            last_updated TEXT,
73            created_at TEXT,
74            FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
75          )
76        ''');
77
78        // 4. NOTES
79        await db.execute('''
80          CREATE TABLE notes (
81            id INTEGER PRIMARY KEY AUTOINCREMENT,
82            image_id TEXT NOT NULL,
83            content TEXT NOT NULL,
84            category TEXT NOT NULL,
85            created_at TEXT,
86            norm_x REAL DEFAULT 0.5,
87            norm_y REAL DEFAULT 0.5,
88            norm_width REAL DEFAULT 0.0,
89            norm_height REAL DEFAULT 0.0,
90            analysis_data TEXT,
91            crop_file_path TEXT,
92            status TEXT DEFAULT 'pending',
93            FOREIGN KEY (image_id) REFERENCES images (id) ON DELETE CASCADE
94          )
95        ''');
96      },
97    );
98  }
99}