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

/*!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 poll_app
CREATE DATABASE IF NOT EXISTS `poll_app` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `poll_app`;

-- Dumping structure for table poll_app.audit_logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unikāls žurnāla ieraksta identifikators',
  `entity_type` varchar(50) NOT NULL COMMENT 'objekta tips (piem., poll, user, vote)',
  `entity_id` int(11) NOT NULL COMMENT 'konkrētā objekta ID',
  `action` varchar(20) NOT NULL COMMENT 'veiktā darbība (create, update, delete u.c.)',
  `performed_by` int(11) DEFAULT NULL COMMENT 'lietotājs, kurš veica darbību',
  `performed_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'darbības izpildes laiks',
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'papildu informācija JSON formātā par darbību',
  PRIMARY KEY (`id`),
  KEY `performed_by` (`performed_by`),
  CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Žurnāls sistēmas darbībām – saglabā informāciju par izmaiņām un lietotāju veiktajām darbībām (audit trail).';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.locations
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unikāls atrašanās vietas ID',
  `country` varchar(100) NOT NULL COMMENT 'valsts nosaukums',
  `city` varchar(100) DEFAULT NULL COMMENT 'pilsēta',
  `region` varchar(100) DEFAULT NULL COMMENT 'reģions / novads',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'ieraksta izveides datums',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Glabā ģeogrāfisko informāciju par lietotāju atrašanās vietām.';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.polls
CREATE TABLE IF NOT EXISTS `polls` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unikāls aptaujas ID',
  `author_id` int(11) DEFAULT NULL COMMENT 'aptaujas autors',
  `title` varchar(255) NOT NULL COMMENT 'aptaujas nosaukums',
  `description` text DEFAULT NULL COMMENT 'aptaujas apraksts',
  `is_public` tinyint(1) DEFAULT 1 COMMENT 'vai aptauja ir publiski pieejama',
  `access_token` text DEFAULT NULL COMMENT 'piekļuves tokens privātām aptaujām',
  `allow_anonymous` tinyint(1) DEFAULT 0 COMMENT 'vai atļauta anonīma balsošana',
  `allow_multiple_votes` tinyint(1) DEFAULT 0 COMMENT 'vai atļautas vairākas balsis',
  `max_choices` int(11) DEFAULT 1 COMMENT 'maksimālais izvēļu skaits vienā balsojumā',
  `start_at` timestamp NULL DEFAULT NULL COMMENT 'aptaujas sākuma laiks',
  `end_at` timestamp NULL DEFAULT NULL COMMENT 'aptaujas beigu laiks',
  `status` varchar(20) DEFAULT 'draft' COMMENT 'aptaujas statuss (draft, active, closed)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'izveides datums',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'pēdējo izmaiņu datums',
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `polls_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aptauju pamatinformācija – definē aptaujas saturu, iestatījumus un statusu.';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.poll_options
CREATE TABLE IF NOT EXISTS `poll_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'nikāls atbildes varianta ID',
  `poll_id` int(11) DEFAULT NULL COMMENT 'saistītā aptauja',
  `label` varchar(255) NOT NULL COMMENT 'atbildes teksts',
  `position` int(11) DEFAULT 0 COMMENT 'atbildes secība attēlošanā',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'izveides datums',
  PRIMARY KEY (`id`),
  KEY `poll_options_ibfk_1` (`poll_id`),
  CONSTRAINT `poll_options_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=148 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aptauju atbilžu varianti, kas pieder konkrētai aptaujai.';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.poll_shares
CREATE TABLE IF NOT EXISTS `poll_shares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) DEFAULT NULL,
  `shared_with_user_id` int(11) DEFAULT NULL,
  `share_token` text DEFAULT NULL,
  `permissions` varchar(20) DEFAULT 'view',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`),
  KEY `shared_with_user_id` (`shared_with_user_id`),
  CONSTRAINT `poll_shares_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`),
  CONSTRAINT `poll_shares_ibfk_2` FOREIGN KEY (`shared_with_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data exporting was unselected.

-- Dumping structure for table poll_app.roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unikāls lomas ID',
  `name` varchar(50) NOT NULL COMMENT 'lomas nosaukums (piem., admin, user)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Lietotāju lomas un to nosaukumi sistēmā.';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unikāls lietotāja ID',
  `username` varchar(100) NOT NULL COMMENT 'lietotājvārds',
  `email` varchar(255) NOT NULL COMMENT 'e-pasta adrese',
  `password_hash` text NOT NULL COMMENT 'paroles hash',
  `bio` text DEFAULT NULL COMMENT 'lietotāja apraksts',
  `location_id` int(10) unsigned DEFAULT NULL COMMENT 'lietotāja atrašanās vieta',
  `name` varchar(100) DEFAULT NULL COMMENT 'pilns vārds',
  `role_id` int(11) DEFAULT NULL COMMENT 'lietotāja loma',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'konta izveides datums',
  `last_login` timestamp NULL DEFAULT NULL COMMENT 'pēdējās pieslēgšanās laiks',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `role_id` (`role_id`),
  KEY `fk_users_location` (`location_id`),
  CONSTRAINT `fk_users_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Sistēmas lietotāju konti un to pamatinformācija.';

-- Data exporting was unselected.

-- Dumping structure for table poll_app.votes
CREATE TABLE IF NOT EXISTS `votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unikāls balsojuma ID',
  `poll_id` int(11) DEFAULT NULL COMMENT 'aptauja, kurā balso',
  `option_id` int(11) DEFAULT NULL COMMENT 'izvēlētais atbildes variants',
  `user_id` int(11) DEFAULT NULL COMMENT 'lietotājs, kurš balsoja',
  `voter_ip` varchar(100) DEFAULT NULL COMMENT 'balsotāja IP adrese',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'balsošanas laiks',
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'papildu balsojuma dati JSON formātā',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_vote_user_option` (`poll_id`,`option_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `votes_ibfk_2` (`option_id`),
  CONSTRAINT `votes_ibfk_1` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`),
  CONSTRAINT `votes_ibfk_2` FOREIGN KEY (`option_id`) REFERENCES `poll_options` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Saglabā lietotāju balsis aptaujās.';

-- Data exporting was unselected.

/*!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) */;