-- https://www.phpmyadmin.net/
--

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



--
-- Database: `church`
--

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs`
(
    `id`         bigint(20) UNSIGNED NOT NULL,
    `uuid`       varchar(255) NOT NULL,
    `connection` text         NOT NULL,
    `queue`      text         NOT NULL,
    `payload`    longtext     NOT NULL,
    `exception`  longtext     NOT NULL,
    `failed_at`  timestamp    NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations`
(
    `id`        int(10) UNSIGNED NOT NULL,
    `migration` varchar(255) NOT NULL,
    `batch`     int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens`
(
    `email`      varchar(255) NOT NULL,
    `token`      varchar(255) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `personal_access_tokens`
--

CREATE TABLE `personal_access_tokens`
(
    `id`             bigint(20) UNSIGNED NOT NULL,
    `tokenable_type` varchar(255) NOT NULL,
    `tokenable_id`   bigint(20) UNSIGNED NOT NULL,
    `name`           varchar(255) NOT NULL,
    `token`          varchar(64)  NOT NULL,
    `abilities`      text DEFAULT NULL,
    `last_used_at`   timestamp NULL DEFAULT NULL,
    `expires_at`     timestamp NULL DEFAULT NULL,
    `created_at`     timestamp NULL DEFAULT NULL,
    `updated_at`     timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `userdetails`
--

CREATE TABLE `userdetails`
(
    `UserDetailsID` int(10) NOT NULL,
    `UserID`        int(10) NOT NULL,
    `FirstName`     varchar(50) NOT NULL,
    `LastName`      varchar(50) DEFAULT NULL,
    `DateOfBirth`   date        NOT NULL,
    `Province`      varchar(20) DEFAULT NULL,
    `Gender`        varchar(6)  NOT NULL,
    `Facilitator`   bit(1)      NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `userdetails`
--

INSERT INTO `userdetails` (`UserDetailsID`, `UserID`, `FirstName`, `LastName`, `DateOfBirth`, `Province`, `Gender`,
                           `Facilitator`)
VALUES (1, 12, 'Tebogos', 'Sebake', '2014-03-09', 'Limpopo', 'Male', b'1'),
       (2, 11, 'Thakgalo', 'Sebake', '2023-03-27', 'Limpopo', 'Male', b'0'),
       (4, 18, 'Bongani', 'Phasha', '2021-09-28', 'Western Cape', 'Female', b'0');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users`
(
    `UserID`     int(10) NOT NULL,
    `Password`   varchar(200) NOT NULL,
    `created_at` timestamp    NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime              DEFAULT current_timestamp() ON UPDATE current_timestamp ()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`UserID`, `Password`, `created_at`, `updated_at`)
VALUES (11, '1', '2024-03-27 05:51:31', '2024-03-27 13:13:57'),
       (12, '1', '2024-03-27 06:32:49', '2024-03-27 08:38:53'),
       (18, '1234', '2024-03-27 11:47:09', '2024-03-27 13:47:09'),
       (19, '1234', '2024-03-27 19:52:49', '2024-03-27 21:52:49');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
    ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`);

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

--
-- Indexes for table `password_reset_tokens`
--
ALTER TABLE `password_reset_tokens`
    ADD PRIMARY KEY (`email`);

--
-- Indexes for table `personal_access_tokens`
--
ALTER TABLE `personal_access_tokens`
    ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  ADD KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`);

--
-- Indexes for table `userdetails`
--
ALTER TABLE `userdetails`
    ADD PRIMARY KEY (`UserDetailsID`),
  ADD KEY `UserID` (`UserID`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
    ADD PRIMARY KEY (`UserID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
    MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
    MODIFY `id` int (10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `personal_access_tokens`
--
ALTER TABLE `personal_access_tokens`
    MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `userdetails`
--
ALTER TABLE `userdetails`
    MODIFY `UserDetailsID` int (10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
    MODIFY `UserID` int (10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `userdetails`
--
ALTER TABLE `userdetails`
    ADD CONSTRAINT `userdetails_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON UPDATE CASCADE;
COMMIT;



-- CREATE TABLE users
-- (
--     user_id   INT         NOT NULL AUTO_INCREMENT,
--     password  VARCHAR(30) NOT NULL,
--     status_id INT         NOT NULL,
--     PRIMARY KEY (user_id)
-- ) ENGINE=InnoDB AUTO_INCREMENT=8458 DEFAULT CHARSET=latin1;

CREATE TABLE user_status
(
    user_status_id int(11) NOT NULL AUTO_INCREMENT,
    status_desc     varchar(20) NOT NULL,
    PRIMARY KEY (user_status_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `user_status` (`user_status_id`, `status_des`)
VALUES (1, 'Active'),
       (2, 'Not Active');


CREATE TABLE gender
(
    gender_id   int(11) NOT NULL AUTO_INCREMENT,
    gender_desc varchar(20) NOT NULL,
    PRIMARY KEY (gender_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `gender` (`gender_id`, `gender_desc`)
VALUES (1, 'Female'),
       (2, 'Male');

-- funeral mpoho CONFERENCE
CREATE TABLE activity_desc
(
    activity_desc_id   INT          NOT NULL AUTO_INCREMENT,
    activity_desc varchar(20) NOT NULL,
    PRIMARY KEY (activity_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `activity_desc` (`activity_desc_id`, `activity_desc`)
VALUES (1, 'Funeral'),
       (2, 'Function'),
       (3, 'Exam Prayer'),
       (4, 'Mpoho'),
       (5, 'Easter Conference'),
       (6, 'September Conference');

CREATE TABLE member_type -- mass, executive
(
    member_type_id   int(11) NOT NULL AUTO_INCREMENT,
    member_type_desc varchar(100) NOT NULL,
    PRIMARY KEY (member_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `member_type` (`member_type_id`, `member_type_desc`)
VALUES (1, 'Mass'),
       (2, 'Executive');

CREATE TABLE executive_types -- bec
(
    exec_type_id   int(11) NOT NULL AUTO_INCREMENT,
    exec_type_desc varchar(20) NOT NULL,
    PRIMARY KEY (exec_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `member_type` (`exec_type_id`, `exec_type_desc`)
VALUES (1, 'BEC (Branch Executive Committee)'),
       (2, 'Male Executive'),
       (3, 'Female Executive'),
       (4, 'Secretary');

-- active rescheduled , cancelled , rescheduled with a date , rescheduled without a date
CREATE TABLE activity_status
(
    activity_status_id   int(11) NOT NULL AUTO_INCREMENT,
    activity_status_desc varchar(20) NOT NULL,
    PRIMARY KEY (exec_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `activity_status` (`activity_status_id`, `activity_status_desc`)
VALUES (1, 'Active'),
       (2, 'Rescheduled'),
       (3, 'Reschedule with a date'),
       (4, 'Reschedule without a date');


CREATE TABLE members
(
    member_id       INT                                   NOT NULL AUTO_INCREMENT,
    password        VARCHAR(30) DEFAULT NULL,
    title           VARCHAR(30)                           NOT NULL,
    first_name      VARCHAR(30)                           NOT NULL,
    second_name     VARCHAR(30),
    last_name       VARCHAR(30)                           NOT NULL,
    email           VARCHAR(30)                           NOT NULL,
    cell_phone      VARCHAR(15)                           NOT NULL,
    exec_type_id    INT         DEFAULT NULL,
    status_id       INT                                   NOT NULL,
    member_type_id  INT                                   NOT NULL,
    gender_id       INT                                   NOT NULL,
    last_updated_by timestamp   default current_timestamp not null,
    captured_date   timestamp   default current_timestamp not null,
    captured_by     VARCHAR(30)                           NOT NULL,
    last_updated_by VARCHAR(30)                           NOT NULL,
    PRIMARY KEY (member_id),
--     FOREIGN KEY (user_id) REFERENCES users (user_id),
    UNIQUE (email),
    UNIQUE (cell_phone)
) ENGINE=InnoDB AUTO_INCREMENT=7546 DEFAULT CHARSET=latin1;

CREATE TABLE addresses
(
    address_id   INT          NOT NULL AUTO_INCREMENT,
    member_id    INT          NOT NULL,
    address_type VARCHAR(30)  NOT NULL, -- home / school /
    address1     VARCHAR(100) NOT NULL,
    address2     VARCHAR(100),
    suburb       VARCHAR(30)  NOT NULL,
    city         VARCHAR(30)  NOT NULL,
    province_id  INT          NOT NULL,
    postal_code  VARCHAR(30)  NOT NULL,
    PRIMARY KEY (address_id),
    FOREIGN KEY (member_id) REFERENCES members (member_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;



CREATE TABLE activities
(
    activity_id                                  INT         NOT NULL AUTO_INCREMENT,
    activity_type                                VARCHAR(30) NOT NULL,         -- trip / event /
    activity_origin                              VARCHAR(30) NOT NULL,         -- internal / external
    activity_desc_id                             INT         NOT NULL,         -- funeral / Mpoho
    activity_dedicated_by                        INT         NOT NULL,         -- someone in the DB exec or secretary will search in the db from members
    activity_coordinator                         INT         NOT NULL,         -- someone in the DB exec or secretary will search in the db
    start_date                                   timestamp   not null,
    revised_start_date                           timestamp            default null,
    end_date                                     timestamp   not null,
    revised_end_date                             timestamp            default null,
    transport_mode                               INT NULL,
    activity_total_cost                          VARCHAR(30) NOT NULL,
    num_of_cars                                  INT NULL,
    total_num_of_seats                           INT NULL,
    seats_occupied                               INT NULL,
    activity_comments                            VARCHAR(350)         DEFAULT NULL,
    females_attended                             INT NULL,
    males_attended                               INT NULL,
    females_executives_attended                  INT NULL,
    male_executives_attended                     INT NULL,
    num_of_females_worn_khaki                    INT NULL,
    num_of_males_worn_khaki                      INT NULL,
    num_of_females_worn_uniform_structure        INT NULL,
    num_of_males_worn_uniform_structure          INT NULL,
    contribution_amounts_from_mass               INT NULL,
    contribution_amounts_from_BEC                INT NULL,
    contribution_amounts_from_females_executives INT NULL,
    contribution_amounts_from_male_executives    INT NULL,
    activity_status                              INT         NOT NULL DEFAULT 1, -- rescheduled,Cancelled,,
    last_updated                                 timestamp   not null,
    captured_date                                timestamp   not null,
    captured_by                                  VARCHAR(30) not null,
    PRIMARY KEY (activity_id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

CREATE TABLE meetings
(
    meeting_id             INT                                   NOT NULL AUTO_INCREMENT,
    meeting_type           VARCHAR(30)                           NOT NULL,
    meeting_agenda         VARCHAR(1000)                         NOT NULL,
    meeting_recommendation VARCHAR(350),
    meeting_conductor      VARCHAR(30)                           NOT NULL, -- someone in the DB exec or secretary will search in the db from members
    start_date             timestamp                             not null,
    end_date               timestamp                             not null,
    meeting_status         INT DEFAULT  1, -- active
    last_updated           timestamp    not null,
    captured_date          timestamp    not null,
    captured_by            INT not NULL,
    PRIMARY KEY (meeting_id)
--     FOREIGN KEY (attendees_id) REFERENCES attendees (attendees_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE attendees
(
    attendees_id  INT                                   NOT NULL AUTO_INCREMENT,
    member_id     INT                                   NOT NULL,
    activity_id   INT NULL,
    meeting_id    INT NULL,
    apology       VARCHAR(30), -- YES / NO
    last_updated  timestamp    not null,
    captured_date timestamp    not null,
    captured_by   int not null,
    PRIMARY KEY (attendees_id),
    FOREIGN KEY (member_id) REFERENCES members (member_id),
    FOREIGN KEY (activity_id) REFERENCES activities (activity_id),
    FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE documents
(
    document_id    INT         NOT NULL AUTO_INCREMENT,
    document_name  VARCHAR(30) NOT NULL,
    activity_id    INT NULL,
    meeting_id     INT NULL,
    normal_docs   VARCHAR(5)  NOT NULL, -- sermons or minutes from meeting
    document_title VARCHAR(30) NOT NULL,
    captured_by     VARCHAR(30) NOT NULL,
    captured_date   timestamp     default current_timestamp not null,
    PRIMARY KEY (document_id),
    FOREIGN KEY (activity_id) REFERENCES activities (activity_id),
    FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;


CREATE TABLE location
(
    location_id INT          NOT NULL AUTO_INCREMENT,
    activity_id INT Default Null,
    meeting_id  INT Default Null,
    province_id INT          NOT NULL,
    address1        VARCHAR(100) NOT NULL,
    address2        VARCHAR(100),
    city        VARCHAR(100) NOT NULL,
    suburb      VARCHAR(30)  NOT NULL,
    postal_code VARCHAR(30)  NOT NULL,
    PRIMARY KEY (location_id),
    FOREIGN KEY (activity_id) REFERENCES activities (activity_id),
    FOREIGN KEY (meeting_id) REFERENCES meetings (meeting_id),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE months
(
    month_id   int(11) NOT NULL AUTO_INCREMENT,
    month_name varchar(20) NOT NULL,
    PRIMARY KEY (month_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;





