-- ============================================
-- DATABASE: Sistem Especialista Diagnostika Moras Animal (Karau)
-- MAP Municipio Baucau - Timor-Leste
-- ============================================

CREATE DATABASE IF NOT EXISTS `expert_karau` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `expert_karau`;

-- ----------------------------
-- Tabela Admin
-- ----------------------------
CREATE TABLE `admin` (
  `id_admin` INT(10) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(30) NOT NULL,
  `password` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`id_admin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `admin` (`username`, `password`) VALUES ('admin', 'admin');

-- ----------------------------
-- Tabela Sintoma
-- ----------------------------
CREATE TABLE `sintoma` (
  `id_sintoma` INT(10) NOT NULL AUTO_INCREMENT,
  `kode_sintoma` VARCHAR(30) NOT NULL,
  `naran_sintoma` VARCHAR(50) NOT NULL,
  `deskrisaun` VARCHAR(50),
  PRIMARY KEY (`id_sintoma`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `sintoma` (`kode_sintoma`, `naran_sintoma`, `deskrisaun`) VALUES
('S01', 'Manas aas', 'Temperatura isin aas liu 39°C'),
('S02', 'La han', 'Karau la nia apetitu atu han'),
('S03', 'Ibun rahun', 'Rahun/bura iha ibun no ain'),
('S04', 'Ain inxadu', 'Ain inxadu no moras bainhira toke'),
('S05', 'Ain fraku', 'La bele halai no ain nakukun'),
('S06', 'Isi iha rahun', 'Isin rahun no molok'),
('S07', 'Matan vermellu', 'Matan sai vermellu no inxadu'),
('S08', 'Kolen diarea', 'Kolen muda no likidu'),
('S09', 'Susar iis', 'Iis ho difikuldade no iis lais'),
('S10', 'Tilun inxadu', 'Tilun inxadu no rahun'),
('S11', 'Fulun koles', 'Fulun muda koles/nakuak'),
('S12', 'Ninin fraku', 'Ninin fraku no susar tuun'),
('S13', 'Isin nakukun', 'Isin hamriik/nakukun'),
('S14', 'Matan namele', 'Matan rahun namele no buras'),
('S15', 'Kabas moras', 'Kabas inxadu no moras bainhira toke');

-- ----------------------------
-- Tabela Moras
-- ----------------------------
CREATE TABLE `moras` (
  `id_moras` INT(10) NOT NULL AUTO_INCREMENT,
  `kode_moras` VARCHAR(50) NOT NULL,
  `naran_moras` VARCHAR(150) NOT NULL,
  `deskrisaun` VARCHAR(150),
  PRIMARY KEY (`id_moras`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `moras` (`kode_moras`, `naran_moras`, `deskrisaun`) VALUES
('M01', 'Foot and Mouth Disease (FMD)', 'Moras viral ne ebe afeta animal hoof no ibun, rahun iha ain no ibun'),
('M02', 'Hemorrhagic Septicemia (HS)', 'Moras bakteria grave ne ebe kauza moras dadauk iha animal'),
('M03', 'Brucellosis', 'Moras bakteria ne ebe afeta sistema reproduksaun karau'),
('M04', 'Bovine Respiratory Disease (BRD)', 'Moras respiratory sistema iha karau, susar iis'),
('M05', 'Theileriosis', 'Moras parasita ne ebe transmite hosi carraça, afeta selula ran');

-- ----------------------------
-- Tabela Rules CF
-- ----------------------------
CREATE TABLE `rules_cf` (
  `id_rules` INT(10) NOT NULL AUTO_INCREMENT,
  `id_moras` INT(10) NOT NULL,
  `id_sintoma` INT(10) NOT NULL,
  `nilai_cf` DECIMAL(4,2) NOT NULL COMMENT 'Nilai CF pakar (0.0 - 1.0)',
  PRIMARY KEY (`id_rules`),
  FOREIGN KEY (`id_moras`) REFERENCES `moras`(`id_moras`) ON DELETE CASCADE,
  FOREIGN KEY (`id_sintoma`) REFERENCES `sintoma`(`id_sintoma`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FMD Rules
INSERT INTO `rules_cf` (`id_moras`, `id_sintoma`, `nilai_cf`) VALUES
(1, 1, 0.8),  -- Manas aas -> FMD
(1, 3, 0.9),  -- Ibun rahun -> FMD
(1, 4, 0.9),  -- Ain inxadu -> FMD
(1, 2, 0.6),  -- La han -> FMD
(1, 6, 0.7),  -- Isi rahun -> FMD
-- HS Rules
(2, 1, 0.9),  -- Manas aas -> HS
(2, 9, 0.8),  -- Susar iis -> HS
(2, 12, 0.7), -- Ninin fraku -> HS
(2, 2, 0.7),  -- La han -> HS
(2, 13, 0.8), -- Isin nakukun -> HS
-- Brucellosis Rules
(3, 15, 0.8), -- Kabas moras -> Brucellosis
(3, 2, 0.6),  -- La han -> Brucellosis
(3, 12, 0.7), -- Ninin fraku -> Brucellosis
-- BRD Rules
(4, 9, 0.9),  -- Susar iis -> BRD
(4, 1, 0.7),  -- Manas aas -> BRD
(4, 2, 0.6),  -- La han -> BRD
(4, 14, 0.7), -- Matan namele -> BRD
-- Theileriosis Rules
(5, 1, 0.8),  -- Manas aas -> Theileriosis
(5, 7, 0.8),  -- Matan vermellu -> Theileriosis
(5, 11, 0.7), -- Fulun koles -> Theileriosis
(5, 2, 0.6),  -- La han -> Theileriosis
(5, 5, 0.7);  -- Ain fraku -> Theileriosis

-- ----------------------------
-- Tabela Diagnosa
-- ----------------------------
CREATE TABLE `diagnosa` (
  `id_diagnosa` INT(10) NOT NULL AUTO_INCREMENT,
  `data_diagnosa` DATE NOT NULL,
  `resultadu_moras` INT(10),
  `valor_cf` DECIMAL(5,4),
  PRIMARY KEY (`id_diagnosa`),
  FOREIGN KEY (`resultadu_moras`) REFERENCES `moras`(`id_moras`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Tabela Rezultadu
-- ----------------------------
CREATE TABLE `rezultadu` (
  `id_rezultadu` INT(10) NOT NULL AUTO_INCREMENT,
  `naran_user` VARCHAR(100) NOT NULL,
  `id_moras` INT(10),
  `valor_cf` DECIMAL(5,4),
  `dikrisaun` VARCHAR(500),
  `data_rezultadu` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_rezultadu`),
  FOREIGN KEY (`id_moras`) REFERENCES `moras`(`id_moras`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;