151 lines
4.6 KiB
Go
151 lines
4.6 KiB
Go
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"log"
|
|
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
func Init(path string) (*sql.DB, error) {
|
|
db, err := sql.Open("sqlite", path+"?_foreign_keys=on&_journal_mode=WAL")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Ping(); err != nil {
|
|
return nil, err
|
|
}
|
|
log.Printf("✓ SQLite connecté : %s", path)
|
|
return db, nil
|
|
}
|
|
|
|
func Migrate(db *sql.DB) error {
|
|
migrations := []string{
|
|
sqlCreateUsers,
|
|
sqlCreateProperties,
|
|
sqlCreateCalendarEvents,
|
|
sqlCreateCategories,
|
|
sqlCreateTransactions,
|
|
sqlCreateDocuments,
|
|
sqlCreateFiscalExports,
|
|
sqlCreateIcalSyncLog,
|
|
sqlSeedCategories,
|
|
}
|
|
for _, m := range migrations {
|
|
if _, err := db.Exec(m); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
log.Println("✓ Migrations appliquées")
|
|
return nil
|
|
}
|
|
|
|
const sqlCreateUsers = `
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`
|
|
|
|
const sqlCreateProperties = `
|
|
CREATE TABLE IF NOT EXISTS properties (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
address TEXT NOT NULL,
|
|
type TEXT NOT NULL CHECK(type IN ('airbnb','longterm')),
|
|
bank_account TEXT,
|
|
ical_url TEXT,
|
|
notes TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`
|
|
|
|
const sqlCreateCalendarEvents = `
|
|
CREATE TABLE IF NOT EXISTS calendar_events (
|
|
id TEXT PRIMARY KEY,
|
|
property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
|
|
title TEXT,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
source TEXT NOT NULL CHECK(source IN ('airbnb','manual')),
|
|
ical_uid TEXT,
|
|
notes TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(property_id, ical_uid)
|
|
);`
|
|
|
|
const sqlCreateCategories = `
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL CHECK(type IN ('income','expense')),
|
|
tax_deductible INTEGER DEFAULT 0,
|
|
description TEXT
|
|
);`
|
|
|
|
const sqlCreateTransactions = `
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id TEXT PRIMARY KEY,
|
|
property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
|
|
category_id TEXT REFERENCES categories(id),
|
|
type TEXT NOT NULL CHECK(type IN ('income','expense')),
|
|
amount REAL NOT NULL,
|
|
date DATE NOT NULL,
|
|
description TEXT,
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`
|
|
|
|
const sqlCreateDocuments = `
|
|
CREATE TABLE IF NOT EXISTS documents (
|
|
id TEXT PRIMARY KEY,
|
|
property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
|
|
transaction_id TEXT REFERENCES transactions(id) ON DELETE SET NULL,
|
|
filename TEXT NOT NULL,
|
|
original_name TEXT NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
mime_type TEXT,
|
|
fiscal_year INTEGER,
|
|
uploaded_by TEXT REFERENCES users(id),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`
|
|
|
|
const sqlCreateFiscalExports = `
|
|
CREATE TABLE IF NOT EXISTS fiscal_exports (
|
|
id TEXT PRIMARY KEY,
|
|
property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
|
|
fiscal_year INTEGER NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
generated_by TEXT REFERENCES users(id),
|
|
generated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`
|
|
|
|
const sqlCreateIcalSyncLog = `
|
|
CREATE TABLE IF NOT EXISTS ical_sync_log (
|
|
id TEXT PRIMARY KEY,
|
|
property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
|
|
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
status TEXT NOT NULL CHECK(status IN ('ok','error')),
|
|
events_imported INTEGER DEFAULT 0,
|
|
error_message TEXT
|
|
);`
|
|
|
|
// Catégories de base prêtes à l'emploi
|
|
const sqlSeedCategories = `
|
|
INSERT OR IGNORE INTO categories (id, name, type, tax_deductible) VALUES
|
|
('cat-loyer', 'Loyer perçu', 'income', 0),
|
|
('cat-airbnb', 'Revenu Airbnb', 'income', 0),
|
|
('cat-charges', 'Charges copropriété', 'expense', 1),
|
|
('cat-travaux', 'Travaux & réparations', 'expense', 1),
|
|
('cat-assurance', 'Assurance', 'expense', 1),
|
|
('cat-taxe', 'Taxe foncière', 'expense', 1),
|
|
('cat-interets', 'Intérêts emprunt', 'expense', 1),
|
|
('cat-menage', 'Ménage & entretien', 'expense', 1),
|
|
('cat-gestion', 'Frais de gestion', 'expense', 1),
|
|
('cat-electricite', 'Électricité', 'expense', 1),
|
|
('cat-eau', 'Eau', 'expense', 1),
|
|
('cat-internet', 'Internet', 'expense', 1),
|
|
('cat-autre-dep', 'Autre dépense', 'expense', 0),
|
|
('cat-autre-rev', 'Autre revenu', 'income', 0);`
|