-- CLEAN PPPL SCOREBOARD DATABASE
-- Schema only. No historical matches, balls, teams, or points.
-- tournament_settings will be created with defaults by the application on first use.

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 30, 2026 at 04:52 PM
-- Server version: 10.4.22-MariaDB
-- PHP Version: 8.1.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pppl_scoreboard`
--

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

--
-- Table structure for table `balls`
--

CREATE TABLE `balls` (
  `id` int(11) NOT NULL,
  `match_id` int(11) NOT NULL,
  `innings` tinyint(4) NOT NULL DEFAULT 1,
  `over_no` int(11) NOT NULL DEFAULT 0,
  `ball_no` int(11) NOT NULL DEFAULT 0,
  `runs` int(11) NOT NULL DEFAULT 0,
  `bat_runs` int(11) NOT NULL DEFAULT 0,
  `extra_runs` int(11) NOT NULL DEFAULT 0,
  `extra_type` enum('none','wd','nb') NOT NULL DEFAULT 'none',
  `is_wicket` tinyint(1) NOT NULL DEFAULT 0,
  `is_legal_ball` tinyint(1) NOT NULL DEFAULT 1,
  `display_text` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `balls`
--


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

--
-- Table structure for table `matches`
--

CREATE TABLE `matches` (
  `id` int(11) NOT NULL,
  `match_no` int(11) NOT NULL,
  `team_a_id` int(11) NOT NULL,
  `team_b_id` int(11) NOT NULL,
  `toss_winner_id` int(11) DEFAULT NULL,
  `toss_choice` enum('bat','field') DEFAULT NULL,
  `batting_team_id` int(11) DEFAULT NULL,
  `bowling_team_id` int(11) DEFAULT NULL,
  `innings` tinyint(4) NOT NULL DEFAULT 1,
  `runs` int(11) NOT NULL DEFAULT 0,
  `wickets` int(11) NOT NULL DEFAULT 0,
  `legal_balls` int(11) NOT NULL DEFAULT 0,
  `extras` int(11) NOT NULL DEFAULT 0,
  `target` int(11) DEFAULT NULL,
  `inning_start_time` datetime DEFAULT NULL,
  `inning_elapsed_seconds` int(11) NOT NULL DEFAULT 0,
  `status` enum('not_started','live','innings_break','super_over','completed') NOT NULL DEFAULT 'not_started',
  `winner_team_id` int(11) DEFAULT NULL,
  `result_text` varchar(255) DEFAULT NULL,
  `manual_result` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `matches`
--


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

--
-- Table structure for table `match_innings`
--

CREATE TABLE `match_innings` (
  `id` int(11) NOT NULL,
  `match_id` int(11) NOT NULL,
  `innings_no` tinyint(4) NOT NULL,
  `batting_team_id` int(11) NOT NULL,
  `bowling_team_id` int(11) NOT NULL,
  `runs` int(11) NOT NULL DEFAULT 0,
  `wickets` int(11) NOT NULL DEFAULT 0,
  `legal_balls` int(11) NOT NULL DEFAULT 0,
  `extras` int(11) NOT NULL DEFAULT 0,
  `target` int(11) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `elapsed_seconds` int(11) NOT NULL DEFAULT 0,
  `status` enum('not_started','live','completed') NOT NULL DEFAULT 'not_started',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `match_innings`
--


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

--
-- Table structure for table `points_table`
--

CREATE TABLE `points_table` (
  `id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `played` int(11) NOT NULL DEFAULT 0,
  `won` int(11) NOT NULL DEFAULT 0,
  `lost` int(11) NOT NULL DEFAULT 0,
  `tied` int(11) NOT NULL DEFAULT 0,
  `points` int(11) NOT NULL DEFAULT 0,
  `runs_for` int(11) NOT NULL DEFAULT 0,
  `balls_faced` int(11) NOT NULL DEFAULT 0,
  `runs_against` int(11) NOT NULL DEFAULT 0,
  `balls_bowled` int(11) NOT NULL DEFAULT 0,
  `nrr` decimal(8,3) NOT NULL DEFAULT 0.000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `points_table`
--


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

--
-- Table structure for table `teams`
--

CREATE TABLE `teams` (
  `id` int(11) NOT NULL,
  `team_name` varchar(140) NOT NULL,
  `short_name` varchar(15) NOT NULL,
  `squad_players_count` int(11) NOT NULL DEFAULT 11,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `teams`
--


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

--
-- Table structure for table `tournament_settings`
--

CREATE TABLE `tournament_settings` (
  `id` int(11) NOT NULL,
  `tournament_name` varchar(180) NOT NULL DEFAULT 'Prasannapura Premier League',
  `squad_players_count` int(11) NOT NULL DEFAULT 11,
  `overs` int(11) NOT NULL DEFAULT 8,
  `balls_per_over` int(11) NOT NULL DEFAULT 6,
  `inning_minutes` int(11) NOT NULL DEFAULT 20,
  `display_mode` enum('scoreboard','sponsors','special','organized','managers','star_players','points_table','theme_song','tournament_completed') NOT NULL DEFAULT 'scoreboard',
  `special_message` text DEFAULT NULL,
  `tournament_winner_team_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tournament_settings`
--


--
-- Indexes for dumped tables
--

--
-- Indexes for table `balls`
--
ALTER TABLE `balls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `match_id` (`match_id`,`innings`),
  ADD KEY `created_at` (`created_at`);

--
-- Indexes for table `matches`
--
ALTER TABLE `matches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `team_a_id` (`team_a_id`),
  ADD KEY `team_b_id` (`team_b_id`),
  ADD KEY `toss_winner_id` (`toss_winner_id`),
  ADD KEY `batting_team_id` (`batting_team_id`),
  ADD KEY `bowling_team_id` (`bowling_team_id`),
  ADD KEY `winner_team_id` (`winner_team_id`),
  ADD KEY `status` (`status`),
  ADD KEY `match_no` (`match_no`);

--
-- Indexes for table `match_innings`
--
ALTER TABLE `match_innings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_match_innings` (`match_id`,`innings_no`),
  ADD KEY `batting_team_id` (`batting_team_id`),
  ADD KEY `bowling_team_id` (`bowling_team_id`);

--
-- Indexes for table `points_table`
--
ALTER TABLE `points_table`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `team_id` (`team_id`);

--
-- Indexes for table `teams`
--
ALTER TABLE `teams`
  ADD PRIMARY KEY (`id`),
  ADD KEY `is_active` (`is_active`);

--
-- Indexes for table `tournament_settings`
--
ALTER TABLE `tournament_settings`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `balls`
--
ALTER TABLE `balls`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `matches`
--
ALTER TABLE `matches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `match_innings`
--
ALTER TABLE `match_innings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `points_table`
--
ALTER TABLE `points_table`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `teams`
--
ALTER TABLE `teams`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tournament_settings`
--
ALTER TABLE `tournament_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `balls`
--
ALTER TABLE `balls`
  ADD CONSTRAINT `balls_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `matches`
--
ALTER TABLE `matches`
  ADD CONSTRAINT `matches_ibfk_1` FOREIGN KEY (`team_a_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `matches_ibfk_2` FOREIGN KEY (`team_b_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `matches_ibfk_3` FOREIGN KEY (`toss_winner_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `matches_ibfk_4` FOREIGN KEY (`batting_team_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `matches_ibfk_5` FOREIGN KEY (`bowling_team_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `matches_ibfk_6` FOREIGN KEY (`winner_team_id`) REFERENCES `teams` (`id`);

--
-- Constraints for table `match_innings`
--
ALTER TABLE `match_innings`
  ADD CONSTRAINT `match_innings_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `match_innings_ibfk_2` FOREIGN KEY (`batting_team_id`) REFERENCES `teams` (`id`),
  ADD CONSTRAINT `match_innings_ibfk_3` FOREIGN KEY (`bowling_team_id`) REFERENCES `teams` (`id`);

--
-- Constraints for table `points_table`
--
ALTER TABLE `points_table`
  ADD CONSTRAINT `points_table_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
