-- Kaşmir Finance Backup
-- DB: kasmir_finance
-- Created: 2026-01-04T05:20:57+03:00

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table: categories
-- ----------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_cat_user_type` (`user_id`,`type`),
  CONSTRAINT `fk_cat_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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


-- ----------------------------
-- Table: credit_cards
-- ----------------------------
DROP TABLE IF EXISTS `credit_cards`;
CREATE TABLE `credit_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_card_user` (`user_id`),
  CONSTRAINT `fk_card_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);


-- ----------------------------
-- Table: goal_contributions
-- ----------------------------
DROP TABLE IF EXISTS `goal_contributions`;
CREATE TABLE `goal_contributions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_gc_user_goal` (`user_id`,`goal_id`),
  KEY `fk_gc_goal` (`goal_id`),
  KEY `fk_gc_acc` (`account_id`),
  CONSTRAINT `fk_gc_acc` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_gc_goal` FOREIGN KEY (`goal_id`) REFERENCES `goals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gc_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);


-- ----------------------------
-- Table: goals
-- ----------------------------
DROP TABLE IF EXISTS `goals`;
CREATE TABLE `goals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_goal_user` (`user_id`),
  CONSTRAINT `fk_goal_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);


-- ----------------------------
-- Table: investment_accounts
-- ----------------------------
DROP TABLE IF EXISTS `investment_accounts`;
CREATE TABLE `investment_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_ia_user` (`user_id`),
  KEY `fk_ia_type` (`investment_type_id`),
  CONSTRAINT `fk_ia_type` FOREIGN KEY (`investment_type_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ia_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);


-- ----------------------------
-- Table: investment_trades
-- ----------------------------
DROP TABLE IF EXISTS `investment_trades`;
CREATE TABLE `investment_trades` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_trades_user_acc` (`user_id`,`investment_account_id`),
  KEY `fk_tr_acc` (`investment_account_id`),
  CONSTRAINT `fk_tr_acc` FOREIGN KEY (`investment_account_id`) REFERENCES `investment_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);
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 ('2','1','1','sell','XRP','4.00000000','5.00000000','0.00','2026-01-04',NULL,'2026-01-04 03:21:16',NULL,NULL);


-- ----------------------------
-- Table: transactions
-- ----------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `idx_tx_user_date` (`user_id`,`occurred_on`),
  KEY `idx_tx_user_kind` (`user_id`,`kind`),
  KEY `fk_tx_cat` (`category_id`),
  CONSTRAINT `fk_tx_cat` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tx_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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);
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 ('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);
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 ('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);
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 ('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);
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 ('5','1','income','2026-01-04','25000.00','TRY','5',NULL,'none',NULL,'account','2','2026-01-04 03:41:54',NULL,NULL);
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 ('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);

SET FOREIGN_KEY_CHECKS=1;
