-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for kme
CREATE DATABASE IF NOT EXISTS `kme` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `kme`;

-- Dumping structure for table kme.accounts
CREATE TABLE IF NOT EXISTS `accounts` (
  `account_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'konta id',
  `user_id` int(11) NOT NULL COMMENT 'konta ipasnieks',
  `account_number` varchar(30) NOT NULL COMMENT 'konta nr',
  `balance` decimal(12,2) DEFAULT 0.00 COMMENT 'cik tev naudas',
  `credit_limit` decimal(12,2) DEFAULT 0.00 COMMENT 'kredita limits',
  `created_at` datetime DEFAULT current_timestamp() COMMENT 'konta izveidosanas datums',
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `account_number` (`account_number`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kme.accounts: ~0 rows (approximately)

-- Dumping structure for table kme.cards
CREATE TABLE IF NOT EXISTS `cards` (
  `card_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'karte',
  `account_id` int(11) NOT NULL COMMENT 'konts',
  `card_number` varchar(20) NOT NULL COMMENT 'karta nr',
  `expiry_date` date NOT NULL COMMENT 'termins',
  `cvv` varchar(4) NOT NULL COMMENT 'cvv',
  `card_type` enum('debit','credit') DEFAULT 'debit' COMMENT 'kartes tips',
  `created_at` datetime DEFAULT current_timestamp() COMMENT 'kad izdots',
  PRIMARY KEY (`card_id`),
  UNIQUE KEY `card_number` (`card_number`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `cards_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kme.cards: ~0 rows (approximately)

-- Dumping structure for table kme.transactions
CREATE TABLE IF NOT EXISTS `transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'nu tu jau zini',
  `from_card_id` int(11) DEFAULT NULL COMMENT 'no kuras karte',
  `to_card_id` int(11) DEFAULT NULL COMMENT 'uz kuru karti',
  `from_country` varchar(50) DEFAULT NULL COMMENT 'sutitaja valsts',
  `to_country` varchar(50) DEFAULT NULL COMMENT 'sanemeja valsts',
  `amount_sent` decimal(12,2) NOT NULL COMMENT 'cik daudz',
  `amount_received` decimal(12,2) DEFAULT NULL COMMENT 'cik dabuja',
  `timestamp` datetime DEFAULT current_timestamp() COMMENT 'cikos',
  PRIMARY KEY (`transaction_id`),
  KEY `from_card_id` (`from_card_id`),
  KEY `to_card_id` (`to_card_id`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`from_card_id`) REFERENCES `cards` (`card_id`),
  CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`to_card_id`) REFERENCES `cards` (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kme.transactions: ~0 rows (approximately)

-- Dumping structure for table kme.users
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'lietotaja id',
  `user_type` enum('individual','business') NOT NULL COMMENT 'vai ir privatpersonam/uznemumam',
  `fullname` varchar(100) NOT NULL COMMENT 'vards',
  `pin` varchar(8) NOT NULL COMMENT 'pin kods',
  `remember_user` tinyint(1) DEFAULT 0 COMMENT 'atcereties',
  `created_at` datetime DEFAULT current_timestamp() COMMENT 'kad izveidots',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`fullname`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kme.users: ~0 rows (approximately)

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;