-- https://www.phpmyadmin.net/
--
-- Anamakine: 127.0.0.1
-- Üretim Zamanı: 05 Oca 2026, 00:38:02
-- Sunucu sürümü: 10.4.32-MariaDB
-- PHP Sürümü: 8.2.12

START TRANSACTION;



-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `categories`
--

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('income','expense','account_type','investment_type','transfer_type') NOT NULL,
  `name` varchar(120) NOT NULL,
  `color` varchar(20) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `categories`
--

INSERT INTO `categories` (`id`, `user_id`, `type`, `name`, `color`, `sort_order`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(3, 1, 'expense', 'Yemek', NULL, 0, 1, '2026-01-04 01:23:03', NULL, NULL),
(4, 1, 'expense', 'Kira', NULL, 0, 1, '2026-01-04 01:23:15', NULL, NULL),
(5, 1, 'income', 'Maaş', NULL, 0, 1, '2026-01-04 01:24:57', NULL, NULL),
(6, 1, 'income', 'Kira', NULL, 0, 1, '2026-01-04 01:25:03', NULL, NULL),
(7, 1, 'income', 'Fatura', NULL, 0, 1, '2026-01-04 01:25:08', NULL, NULL),
(8, 1, 'account_type', 'Nakit', NULL, 0, 1, '2026-01-04 02:55:43', NULL, NULL),
(9, 1, 'account_type', 'Banka', NULL, 0, 1, '2026-01-04 02:59:42', NULL, NULL),
(10, 1, 'account_type', 'Diğer', NULL, 0, 1, '2026-01-04 03:00:09', NULL, NULL),
(11, 1, 'investment_type', 'Kripto', NULL, 0, 1, '2026-01-04 03:02:12', NULL, NULL),
(12, 1, 'investment_type', 'BES', NULL, 0, 1, '2026-01-04 03:04:56', NULL, NULL),
(13, 1, 'investment_type', 'Borsa', NULL, 0, 1, '2026-01-04 03:05:34', NULL, NULL),
(14, 1, 'expense', 'Fatura', NULL, 0, 1, '2026-01-04 03:13:37', NULL, NULL),
(15, 1, 'expense', 'WebSite', NULL, 0, 1, '2026-01-04 03:13:49', NULL, NULL),
(16, 1, 'expense', 'Hedef Birikimi', NULL, 0, 1, '2026-01-04 03:28:28', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `credit_cards`
--

CREATE TABLE `credit_cards` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `currency` char(3) NOT NULL DEFAULT 'TRY',
  `card_limit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `opening_debt` decimal(14,2) NOT NULL DEFAULT 0.00,
  `statement_day` tinyint(4) DEFAULT NULL,
  `due_day` tinyint(4) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `credit_cards`
--

INSERT INTO `credit_cards` (`id`, `user_id`, `name`, `currency`, `card_limit`, `opening_debt`, `statement_day`, `due_day`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'Garanti Bonus', 'TRY', 22600.00, 0.00, NULL, NULL, 1, '2026-01-04 03:20:23', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `goals`
--

CREATE TABLE `goals` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `target_amount` decimal(14,2) NOT NULL,
  `target_date` date NOT NULL,
  `status` enum('active','paused','completed','canceled') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `goals`
--

INSERT INTO `goals` (`id`, `user_id`, `name`, `target_amount`, `target_date`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'Haziran Tatili', 20000.00, '2026-07-04', 'active', '2026-01-04 03:28:17', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `goal_contributions`
--

CREATE TABLE `goal_contributions` (
  `id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `goal_id` int(11) NOT NULL,
  `account_id` int(11) DEFAULT NULL,
  `amount` decimal(14,2) NOT NULL,
  `contributed_at` date NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `goal_contributions`
--

INSERT INTO `goal_contributions` (`id`, `user_id`, `goal_id`, `account_id`, `amount`, `contributed_at`, `note`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 1, 2, 500.00, '2026-01-04', NULL, '2026-01-04 03:28:28', NULL, NULL),
(2, 1, 1, 2, 344.00, '2026-01-04', NULL, '2026-01-04 13:21:12', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `investment_accounts`
--

CREATE TABLE `investment_accounts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `investment_type_id` int(11) DEFAULT NULL,
  `currency` char(3) NOT NULL DEFAULT 'TRY',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `investment_accounts`
--

INSERT INTO `investment_accounts` (`id`, `user_id`, `name`, `investment_type_id`, `currency`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'Binance', 11, 'TRY', 1, '2026-01-04 03:20:08', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `investment_trades`
--

CREATE TABLE `investment_trades` (
  `id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `investment_account_id` int(11) NOT NULL,
  `side` enum('buy','sell') NOT NULL,
  `asset_symbol` varchar(20) NOT NULL,
  `quantity` decimal(18,8) NOT NULL,
  `price` decimal(18,8) NOT NULL,
  `fee` decimal(14,2) NOT NULL DEFAULT 0.00,
  `traded_at` date NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `investment_trades`
--

INSERT INTO `investment_trades` (`id`, `user_id`, `investment_account_id`, `side`, `asset_symbol`, `quantity`, `price`, `fee`, `traded_at`, `note`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 1, 'buy', 'XRP', 45.00000000, 100.00000000, 2.50, '2026-01-04', NULL, '2026-01-04 03:20:55', NULL, NULL),
(2, 1, 1, 'sell', 'XRP', 4.00000000, 5.00000000, 0.00, '2026-01-04', NULL, '2026-01-04 03:21:16', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `notes`
--

CREATE TABLE `notes` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(120) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `color` varchar(20) NOT NULL DEFAULT 'yellow',
  `is_pinned` tinyint(1) NOT NULL DEFAULT 0,
  `view_mode` enum('sticky','list') NOT NULL DEFAULT 'sticky',
  `pos_x` int(11) DEFAULT NULL,
  `pos_y` int(11) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `notes`
--

INSERT INTO `notes` (`id`, `user_id`, `title`, `content`, `color`, `is_pinned`, `view_mode`, `pos_x`, `pos_y`, `sort_order`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, '1. not', 'deneme notu', 'yellow', 0, 'sticky', NULL, NULL, 0, '2026-01-05 01:50:05', NULL, NULL);

-- --------------------------------------------------------

--
-- Tablo için tablo yapısı `transactions`
--

CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `kind` enum('income','expense','transfer') NOT NULL,
  `occurred_on` date NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `currency` char(3) NOT NULL DEFAULT 'TRY',
  `category_id` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `source_type` enum('account','credit_card','investment','none') NOT NULL DEFAULT 'none',
  `source_id` int(11) DEFAULT NULL,
  `dest_type` enum('account','credit_card','investment','none') NOT NULL DEFAULT 'none',
  `dest_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Tablo döküm verisi `transactions`
--

INSERT INTO `transactions` (`id`, `user_id`, `kind`, `occurred_on`, `amount`, `currency`, `category_id`, `description`, `source_type`, `source_id`, `dest_type`, `dest_id`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'expense', '2026-01-04', 500.00, 'TRY', 3, NULL, 'account', 1, 'none', NULL, '2026-01-04 03:01:50', NULL, NULL),
(2, 1, 'transfer', '2026-01-04', 500.00, 'TRY', NULL, 'Hesaplar arası transfer', 'account', 2, 'account', 1, '2026-01-04 03:10:01', NULL, NULL),
(3, 1, 'transfer', '2026-01-04', 500.00, 'TRY', NULL, 'Hesaplar arası transfer', 'account', 2, 'account', 1, '2026-01-04 03:14:01', NULL, NULL),
(4, 1, 'expense', '2026-01-04', 500.00, 'TRY', 16, 'Hedef Birikimi • Haziran Tatili', 'account', 2, 'none', NULL, '2026-01-04 03:28:28', NULL, NULL),
(5, 1, 'income', '2026-01-04', 25000.00, 'TRY', 5, NULL, 'none', NULL, 'account', 2, '2026-01-04 03:41:54', NULL, NULL),
(6, 1, 'transfer', '2026-01-04', 1000.00, 'TRY', NULL, 'Yatırıma para gönder', 'account', 1, 'investment', 1, '2026-01-04 04:29:46', NULL, NULL),
(7, 1, 'expense', '2026-01-04', 344.00, 'TRY', 16, 'Hedef Birikimi • Haziran Tatili', 'account', 2, 'none', NULL, '2026-01-04 13:21:12', NULL, NULL);

--
-- Tablo için indeksler `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cat_user_type` (`user_id`,`type`);

--
-- Tablo için indeksler `credit_cards`
--
ALTER TABLE `credit_cards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_card_user` (`user_id`);

--
-- Tablo için indeksler `goals`
--
ALTER TABLE `goals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_goal_user` (`user_id`);

--
-- Tablo için indeksler `goal_contributions`
--
ALTER TABLE `goal_contributions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_gc_user_goal` (`user_id`,`goal_id`),
  ADD KEY `fk_gc_goal` (`goal_id`),
  ADD KEY `fk_gc_acc` (`account_id`);

--
-- Tablo için indeksler `investment_accounts`
--
ALTER TABLE `investment_accounts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ia_user` (`user_id`),
  ADD KEY `fk_ia_type` (`investment_type_id`);

--
-- Tablo için indeksler `investment_trades`
--
ALTER TABLE `investment_trades`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_trades_user_acc` (`user_id`,`investment_account_id`),
  ADD KEY `fk_tr_acc` (`investment_account_id`);

--
-- Tablo için indeksler `notes`
--
ALTER TABLE `notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_notes_user` (`user_id`,`deleted_at`,`is_pinned`,`sort_order`);

--
-- Tablo için indeksler `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tx_user_date` (`user_id`,`occurred_on`),
  ADD KEY `idx_tx_user_kind` (`user_id`,`kind`),
  ADD KEY `fk_tx_cat` (`category_id`);

--
-- Tablo için AUTO_INCREMENT değeri `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- Tablo için AUTO_INCREMENT değeri `credit_cards`
--
ALTER TABLE `credit_cards`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Tablo için AUTO_INCREMENT değeri `goals`
--
ALTER TABLE `goals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Tablo için AUTO_INCREMENT değeri `goal_contributions`
--
ALTER TABLE `goal_contributions`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Tablo için AUTO_INCREMENT değeri `investment_accounts`
--
ALTER TABLE `investment_accounts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Tablo için AUTO_INCREMENT değeri `investment_trades`
--
ALTER TABLE `investment_trades`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Tablo için AUTO_INCREMENT değeri `notes`
--
ALTER TABLE `notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Tablo için AUTO_INCREMENT değeri `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- Tablo kısıtlamaları `categories`
--
ALTER TABLE `categories`
  ADD CONSTRAINT `fk_cat_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `credit_cards`
--
ALTER TABLE `credit_cards`
  ADD CONSTRAINT `fk_card_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `goals`
--
ALTER TABLE `goals`
  ADD CONSTRAINT `fk_goal_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `goal_contributions`
--
ALTER TABLE `goal_contributions`
  ADD CONSTRAINT `fk_gc_acc` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_gc_goal` FOREIGN KEY (`goal_id`) REFERENCES `goals` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_gc_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `investment_accounts`
--
ALTER TABLE `investment_accounts`
  ADD CONSTRAINT `fk_ia_type` FOREIGN KEY (`investment_type_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_ia_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `investment_trades`
--
ALTER TABLE `investment_trades`
  ADD CONSTRAINT `fk_tr_acc` FOREIGN KEY (`investment_account_id`) REFERENCES `investment_accounts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_tr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `notes`
--
ALTER TABLE `notes`
  ADD CONSTRAINT `fk_notes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `fk_tx_cat` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_tx_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

