pulse-api/internal/repository/db.go

package repository

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

func NewDB(databaseURL string) (*sqlx.DB, error) {
	db, err := sqlx.Connect("postgres", databaseURL)
	if err != nil {
		return nil, err
	}

	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)

	return db, nil
}

func RunMigrations(db *sqlx.DB) error {
	migrations := []string{
		`CREATE TABLE IF NOT EXISTS users (
			id SERIAL PRIMARY KEY,
			email VARCHAR(255) UNIQUE NOT NULL,
			username VARCHAR(100) NOT NULL,
			password_hash VARCHAR(255) NOT NULL,
			email_verified BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS habits (
			id SERIAL PRIMARY KEY,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			name VARCHAR(255) NOT NULL,
			description TEXT DEFAULT '',
			color VARCHAR(20) DEFAULT '#6366f1',
			icon VARCHAR(50) DEFAULT 'check',
			frequency VARCHAR(20) DEFAULT 'daily',
			target_days INTEGER[] DEFAULT '{1,2,3,4,5,6,0}',
			target_count INTEGER DEFAULT 1,
			is_archived BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS habit_logs (
			id SERIAL PRIMARY KEY,
			habit_id INTEGER REFERENCES habits(id) ON DELETE CASCADE,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			date DATE NOT NULL,
			count INTEGER DEFAULT 1,
			note TEXT DEFAULT '',
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			UNIQUE(habit_id, date)
		)`,
		`CREATE TABLE IF NOT EXISTS email_tokens (
			id SERIAL PRIMARY KEY,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			token VARCHAR(255) UNIQUE NOT NULL,
			type VARCHAR(20) NOT NULL,
			expires_at TIMESTAMP NOT NULL,
			used_at TIMESTAMP,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS tasks (
			id SERIAL PRIMARY KEY,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			title VARCHAR(255) NOT NULL,
			description TEXT DEFAULT '',
			icon VARCHAR(10) DEFAULT '📋',
			color VARCHAR(7) DEFAULT '#6B7280',
			due_date DATE,
			priority INTEGER DEFAULT 0,
			completed_at TIMESTAMP,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE INDEX IF NOT EXISTS idx_habits_user_id ON habits(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_habit_logs_habit_id ON habit_logs(habit_id)`,
		`CREATE INDEX IF NOT EXISTS idx_habit_logs_date ON habit_logs(date)`,
		`CREATE INDEX IF NOT EXISTS idx_habit_logs_user_date ON habit_logs(user_id, date)`,
		`CREATE INDEX IF NOT EXISTS idx_email_tokens_token ON email_tokens(token)`,
		`CREATE INDEX IF NOT EXISTS idx_email_tokens_user_id ON email_tokens(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date)`,
		`CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(user_id, completed_at)`,
		`DO $$ 
		BEGIN 
			IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='email_verified') THEN
				ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
			END IF;
		END $$;`,
		`ALTER TABLE users ADD COLUMN IF NOT EXISTS telegram_chat_id BIGINT`,
		`ALTER TABLE users ADD COLUMN IF NOT EXISTS notifications_enabled BOOLEAN DEFAULT true`,
		`ALTER TABLE users ADD COLUMN IF NOT EXISTS timezone VARCHAR(50) DEFAULT 'Europe/Moscow'`,
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS reminder_time TIME`,
		`ALTER TABLE habits ADD COLUMN IF NOT EXISTS reminder_time TIME`,
		`CREATE INDEX IF NOT EXISTS idx_users_telegram_chat_id ON users(telegram_chat_id)`,
		// Recurring tasks support
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS is_recurring BOOLEAN DEFAULT false`,
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS recurrence_type VARCHAR(20)`,
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS recurrence_interval INTEGER DEFAULT 1`,
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS recurrence_end_date DATE`,
		`ALTER TABLE tasks ADD COLUMN IF NOT EXISTS parent_task_id INTEGER REFERENCES tasks(id) ON DELETE SET NULL`,
		`CREATE INDEX IF NOT EXISTS idx_tasks_parent_id ON tasks(parent_task_id)`,
		`CREATE INDEX IF NOT EXISTS idx_tasks_recurring ON tasks(is_recurring) WHERE is_recurring = true`,
		// Habit freezes support
		`CREATE TABLE IF NOT EXISTS habit_freezes (
			id SERIAL PRIMARY KEY,
			habit_id INTEGER REFERENCES habits(id) ON DELETE CASCADE,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			start_date DATE NOT NULL,
			end_date DATE NOT NULL,
			reason VARCHAR(255) DEFAULT '',
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE INDEX IF NOT EXISTS idx_habit_freezes_habit ON habit_freezes(habit_id)`,
		`CREATE INDEX IF NOT EXISTS idx_habit_freezes_dates ON habit_freezes(start_date, end_date)`,
		// Habit start_date support
		`ALTER TABLE habits ADD COLUMN IF NOT EXISTS start_date DATE`,
	}

	for _, migration := range migrations {
		if _, err := db.Exec(migration); err != nil {
			return err
		}
	}

	return nil
}

func RunFinanceMigrations(db *sqlx.DB) error {
	migrations := []string{
		`CREATE TABLE IF NOT EXISTS finance_categories (
			id SERIAL PRIMARY KEY,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			name VARCHAR(100) NOT NULL,
			emoji VARCHAR(10) DEFAULT '',
			type VARCHAR(10) NOT NULL,
			budget DECIMAL(12,2),
			color VARCHAR(7) DEFAULT '#6366f1',
			sort_order INTEGER DEFAULT 0,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS finance_transactions (
			id SERIAL PRIMARY KEY,
			user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
			category_id INTEGER REFERENCES finance_categories(id) ON DELETE SET NULL,
			type VARCHAR(10) NOT NULL,
			amount DECIMAL(12,2) NOT NULL,
			description TEXT DEFAULT '',
			date DATE NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE INDEX IF NOT EXISTS idx_finance_categories_user ON finance_categories(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_finance_transactions_user ON finance_transactions(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_finance_transactions_date ON finance_transactions(date)`,
		`CREATE INDEX IF NOT EXISTS idx_finance_transactions_category ON finance_transactions(category_id)`,
		`CREATE INDEX IF NOT EXISTS idx_finance_transactions_user_date ON finance_transactions(user_id, date)`,
	}
	for _, m := range migrations {
		if _, err := db.Exec(m); err != nil {
			return err
		}
	}
	return nil
}